/*
  「プログラムでは日本語入力ができないのでSQL文を力づくで作成」をなんとか対応したプログラム
 
  245行目の
 
  //////////////  漢字を書き込めるようにする、魔法の一行
  PQsetClientEncoding(conn, "SJIS"); // 左の通り、クライアント側エンコード指定
  // もしくは、SQL文として「PQExec(conn, "SET CLIENT_ENCODING TO 'SJIS'")」でも可
  ////////////// 
  
  に注目
*/
 
/*
  g++ -g station_sql.cpp -o station_sql.exe -I"D:\PostgreSQL\pg96\include" -L"D:\PostgreSQL\pg96\lib" -llibpq
 
*/
 
/*
   C:\Users\yrl-user>psql -h localhost -U postgres	← ログイン
   postgres=# \l	
   postgres=# create database cx_dx;
   
   以下をコピペする
   create table station_info (
          serial_number int,
          station_name varchar(40),
          longitude real,
          latitude real,
          bus_terminal boolean,
          bus_number int
	);
 */
 
/*
 
ca_db=# \dt station_info
              リレーションの一覧
 スキーマ |     名前     |    型    |  所有者
----------+--------------+----------+----------
 public   | station_info | テーブル | postgres
(1 行)
 
 
ca_db=# \d station_info
         テーブル "public.station_info"
      列       |          型           | 修飾語
---------------+-----------------------+--------
 serial_number | integer               |
 station_name  | character varying(40) |
 longitude     | real                  |
 latitude      | real                  |
 bus_terminal  | boolean               |
 bus_number    | integer               |
 
*/

#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
 
// 車両状態情報(現在地)
typedef struct location{
  // ちなみに X,Y 軸座標は、→に＋  ↑に＋
  double longitude; // 経度 東経 139.691 X軸  
  double latitude;  // 緯度 北緯 35.698  Y軸 
} LOCATION;
 
// 路線単位の駅番号
typedef struct station_number{
  int line;
  int number;
} STATION_NUMBER,AREA;
 
// 駅の情報を格納する構造体
typedef struct station{
  int serial_number;
  char name[40];
  LOCATION location;
  int intersection;
  STATION_NUMBER station_number[10];
  int person_number; 
  int bus_number; 
  double diff_tokyo;
}STATION;
 
STATION station[] = {
 
  // 中央線  全長約28km 上り14 下り14   2km で 1本というのは悪くない
  {0,"国分寺",{139.480841,35.700123},1,{{0,0}},0,5,-0.01},
  {1,"武蔵小金井",{139.506483,35.701337},1,{{0,1}},0,0,-0.01},
  {2,"東小金井",{139.524837,35.701643},1,{{0,2}},0,0,-0.01},
  {3,"武蔵境",{139.543402,35.702083},1,{{0,3}},0,5,-0.01},
  {4,"三鷹",{139.560325,35.702683},1,{{0,4}},0,0,-0.01},
  {5,"吉祥寺",{139.579765,35.703119},2,{{0,5},{1,0}},0,5,-0.01},
  {6,"西荻窪",{139.599361,35.703842},1,{0,6},0,0,-0.01},
  {7,"荻窪",{139.620109,35.704523},1,{{0,7}},0,5,-0.01},
  {8,"阿佐ヶ谷",{139.635859,35.704872},1,{{0,8}},0,5,-0.01},  
  {9,"高円寺",{139.649664,35.705326},1,{{0,9}},0,0,-0.01},
  {10,"中野",{139.665835,35.705765},1,{{0,10}},0,0,-0.01},
  {11,"新宿",{139.700464,35.689729},4,{{0,11},{2,0},{2,29},{3,23}},0,5,-0.01},
  {12,"四ツ谷",{139.730644,35.686041},1,{{0,12}},0,5,-0.01},
  {13,"御茶ノ水",{139.764955,35.699605},1,{{0,13}},0,0,-0.01},
  {14,"神田",{139.770641,35.691173},1,{{0,14}},0,0,-0.01},
  {15,"東京",{139.766103,35.681391},2,{{0,15},{2,13}},0,5,-0.01},
  // 合計15駅
 
  // 京王井の頭線 全長12.55km 上り6 下り6
 
  //吉祥寺	139.580306	35.702291(既出) {1,0}
  {16,"井の頭公園",{139.583112,35.697304},1,{{1,1}},0,0,-0.01},
  {17,"三鷹台",{139.589298,35.692046},1,{{1,2}},0,0,-0.01},
  {18,"久我山",{139.599308,35.688151},1,{{1,3}},0,5,-0.01},
  {19,"富士見ヶ丘",{139.607072,35.684805},1,{{1,4}},0,0,-0.01},
  {20,"高井戸",{139.615115,35.683253},1,{{1,5}},0,0,-0.01},
  {21,"西永福",{139.634936,35.678918},1,{{1,6}},0,0,-0.01},
  {22,"永福町",{139.6404349,35.6762763},1,{{1,7}},0,5,-0.01},
  {23,"明大前",{139.650352,35.668758},2,{{1,8},{3,18}},0,5,-0.01},
  {24,"東松原",{139.655535,35.662634},1,{{1,9}},0,0,-0.01},
  {25,"新代田",{139.660524,35.662593},1,{{1,10}},0,0,-0.01},
  {26,"下北沢",{139.6649004,35.661563},1,{{1,11}},0,5,-0.01},
  {27,"渋谷",{139.699553,35.6581046},2,{{1,12},{2,3}},0,5,-0.01},
 
  // 山手線 全長34.5km → 上り15 下り15
  //新宿	139.700464	35.689729(既出) {2,0}
  {28,"代々木",{139.702042,35.683061},1,{{2,1}},0,0,-0.01},
  {29,"原宿",{139.702592,35.670646},1,{{2,2}},0,0,-0.01},
  // 渋谷	139.701238	35.658871(既出) {2,3}
  {30,"恵比寿",{139.71007,35.646685},1,{{2,4}},0,0,-0.01},
  {31,"目黒",{139.715775,35.633923},1,{{2,5}},0,0,-0.01},
  {32,"五反田",{139.723822,35.625974},1,{{2,6}},0,0,-0.01},
  {33,"大崎",{139.728439,35.619772},1,{{2,7}},0,0,-0.01},
  {34,"品川",{139.738999,35.62876},1,{{2,8}},0,0,-0.01},
  {35,"田町",{139.747575,35.645736},1,{{2,9}},0,0,-0.01},
  {36,"浜松町",{139.757135,35.655391},1,{{2,10}},0,0,-0.01},
  {37,"新橋",{139.758587,35.666195},1,{{2,11}},0,5,-0.01},	
  {38,"有楽町",{139.763806,35.675441},1,{{2,12}},0,0,-0.01},	
  // 東京	139.766103	35.681391(既出){2,13}
  {39,"神田",{139.770641,35.691173},1,{{2,14}},0,0,-0.01},	
  {40,"秋葉原",{139.773288,35.698619},1,{{2,15}},0,5,-0.01},
  {41,"御徒町",{139.774727,35.707282},1,{{2,16}},0,0,-0.01},
  {42,"上野",{139.777043,35.71379},1,{{2,17}},0,5,-0.01},
  {43,"鶯谷",{139.778015,35.721484},1,{{2,18}},0,0,-0.01},
  {44,"日暮里",{139.771287,35.727908},1,{{2,19}},0,5,-0.01},
  {45,"西日暮里",{139.766857,35.731954},1,{{2,20}},0,0,-0.01},
  {46,"田端",{139.761229,35.737781},1,{{2,21}},0,5,-0.01},
  {47,"駒込",{139.748053,35.736825},1,{{2,22}},0,0,-0.01},
  {48,"巣鴨",{139.739303,35.733445},1,{{2,23}},0,0,-0.01},
  {49,"大塚",{139.728584,35.731412},1,{{2,24}},0,0,-0.01},
  {50,"池袋",{139.711461,35.72913},2,{{2,25},{5,7}},0,5,-0.01},
  {51,"目白",{139.706228,35.720476},1,{{2,26}},0,0,-0.01},
  {52,"高田馬場",{139.703715,35.712677},2,{{2,27},{4,17}},0,5,-0.01},
  {53,"新大久保",{139.700261,35.700875},1,{{2,28}},0,0,-0.01},
  // 新宿 139.700464	35.689729(既出) {2,29}
 
  // 京王線 駅数24  全長15.23km 上り7 下り7
  {54,"府中",{139.4799,35.672245},1,{{3,0}},0,5,-0.01},
  {55,"東府中",{139.495257,35.668766},1,{{3,1}},0,0,-0.01},
  {56,"多磨霊園",{139.502615,35.666197},1,{{3,2}},0,0,-0.01},
  {57,"武蔵野台",{139.511289,35.664159},1,{{3,3}},0,0,-0.01},
  {58,"飛田給",{139.523666,35.660121},1,{{3,4}},0,5,-0.01},
  {59,"西調布",{139.529822,35.657169},1,{{3,5}},0,0,-0.01},
  {60,"調布",{139.543988,35.652181},1,{{3,6}},0,0,-0.01},
  {61,"布田",{139.551557,35.649904},1,{{3,7}},0,0,-0.01},
  {62,"国領",{139.558036,35.650087},1,{{3,8}},0,0,-0.01},
  {63,"柴崎",{139.56658,35.653997},1,{{3,9}},0,0,-0.01},
  {64,"つつじヶ丘",{139.575103,35.657936},1,{{3,10}},0,5,-0.01},
  {65,"仙川",{139.584908,35.662257},1,{{3,11}},0,0,-0.01},
  {66,"千歳烏山",{139.60067,35.667921},1,{{3,12}},0,5,-0.01},
  {67,"芦花公園",{139.608247,35.670479},1,{{3,13}},0,0,-0.01},
  {68,"八幡山",{139.614927,35.669982},1,{{3,14}},0,0,-0.01},
  {69,"上北沢",{139.62329,35.668857},1,{{3,15}},0,0,-0.01},
  {70,"桜上水",{139.63129,35.66768},1,{{3,16}},0,0,-0.01},
  {71,"下高井戸",{139.641372,35.66615},1,{{3,17}},0,0,-0.01},
  // 明大前	139.650352	35.668758(既出){3,18}
  {72,"代田橋",{139.659413,35.671092},1,{{3,19}},0,0,-0.01},
  {73,"笹塚",{139.667251,35.673758},1,{{3,20}},0,5,-0.01},
  {74,"幡ヶ谷",{139.676183,35.677061},1,{{3,21}},0,0,-0.01},
  {75,"初台",{139.686354,35.68123},1,{{3,22}},0,0,-0.01},
  // 新宿	139.699187	35.690163(既出){3,23}
 
  // 西武新宿線 全長20.42km 上り9 下り9 18駅
 
  {76,"小平",{139.488491,35.736963},1,{{4,0}},0,5,-0.01},
  {77,"花小金井",{139.513228,35.726129},1,{{4,1}},0,0,-0.01},
  {78,"田無",{139.539259,35.727307},1,{{4,2}},0,0,-0.01},
  {79,"西武柳沢",{139.552477,35.728621},1,{{4,3}},0,5,-0.01},   // No:79
  {80,"東伏見",{139.563529,35.728761},1,{{4,4}},0,0,-0.01},
  {81,"武蔵関",{139.576417,35.7276},1,{{4,5}},0,0,-0.01},
  {82,"上石神井",{139.592266,35.726189},1,{{4,6}},0,0,-0.01},
  {83,"上井草",{139.602937,35.725326},1,{{4,7}},0,0,-0.01},
  {84,"井荻",{139.615303,35.72469},1,{{4,8}},0,0,-0.01},
  {85,"下井草",{139.624688,35.723852},1,{{4,9}},0,5,-0.01},
  {86,"鷺ノ宮",{139.63892,35.722605},1,{{4,10}},0,0,-0.01},
  {87,"都立家政",{139.644839,35.722313},1,{{4,11}},0,0,-0.01},
  {88,"野方",{139.652733,35.719658},1,{{4,12}},0,0,-0.01},
  {89,"沼袋",{139.663841,35.719458},1,{{4,13}},0,5,-0.01},       // No:89
  {90,"新井薬師前",{139.672582,35.715778},1,{{4,14}},0,0,-0.01}, // No:90
  {91,"中井",{139.686967,35.715106},1,{{4,15}},0,0,-0.01},
  {92,"下落合",{139.695391,35.715846},1,{{4,16}},0,5,-0.01},
  // 高田馬場	139.703715	35.712677(既出){4,17}
 
  // 西武池袋線 全長8.2km  上り4 下り4  8駅
  {93,"富士見台",{139.62969,35.735867},1,{{5,0}},0,5,-0.01},
  {94,"中村橋",{139.637456,35.736767},1,{{5,1}},0,0,-0.01},
  {95,"練馬",{139.654368,35.737893},1,{{5,2}},0,0,-0.01},
  {96,"桜台",{139.662602,35.738797},1,{{5,3}},0,0,-0.01},
  {97,"江古田",{139.672814,35.737557},1,{{5,4}},0,0,-0.01},  //
  {98,"東長崎",{139.683294,35.73003},1,{{5,5}},0,5,-0.01},
  {99,"椎名町",{139.694363,35.726572},1,{{5,6}},0,0,-0.01},
  // 池袋	139.711461	35.72913(既出){5,7}
 
  {-1,"",{0,0},0} // 終了
};
 
const char *conninfo = "host=localhost user=postgres password=x-axexoxe dbname=cx_dx";
PGconn *conn;
 
int main()
{
  // データベースとの接続を確立する 
  conn = PQconnectdb(conninfo);
 
  // 先ずはデータの全消去
  PGresult *res = PQexec(conn, "DELETE FROM station_info;");
  PQclear(res);
  
  // バックエンドとの接続確立に成功したかを確認する */
  if (PQstatus(conn) != CONNECTION_OK){
	fprintf(stderr, "Connection to database failed: %s",
			PQerrorMessage(conn));
  }
 
  //////////////  漢字を書き込めるようにする、魔法の一行
  PQsetClientEncoding(conn, "SJIS"); // 左の通り、クライアント側エンコード指定
  // もしくは、SQL文として「PQExec(conn, "SET CLIENT_ENCODING TO 'SJIS'")」でも可
  ////////////// 
 
  for (int i = 0; i < 100; i++){
	char stringSQL[256] = {0};
	char tfflag[2] = {0};
	
	if (station[i].bus_number > 0){
	  strcpy(tfflag, "t");
	} else {
	  strcpy(tfflag, "f");
	}
	
	sprintf(stringSQL, "INSERT INTO station_info(serial_number,longitude, latitude, bus_number, bus_terminal, station_name) VALUES(%d,%f,%f,%d,'%s','%s');",
			station[i].serial_number,
			station[i].location.longitude,
			station[i].location.latitude,
			station[i].bus_number,
			tfflag,
			station[i].name);
	
#if 0
	printf("%s\n",stringSQL);
#endif
	
	PGresult *res = PQexec(conn, stringSQL);
	if (res == NULL || PQresultStatus(res) != PGRES_COMMAND_OK) {
	  // SQLコマンドが失敗した場合
	  fprintf(stderr, "INSERT COMMAND IS FAILED.",
			  PQerrorMessage(conn));
	}
	
	// 値セットが無い場合でも必ず結果をクリアする
	PQclear(res);
  }
  
  return 0;
}