#include "sqlite3.h"
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "common.h"

#define MY_DATABASE "ysp.db"

#define CREATE_TBL_CMD "CREATE TABLE IF NOT EXISTS his_tbl(H2 FLOAT,CO FLOAT,\
CH4 FLOAT,C2H4 FLOAT,C2H6 FLOAT,C2H2 FLOAT,TotHyd FLOAT,SmpTm INTEGER,CO2 FLOAT,\
H2O FLOAT,OilTmp FLOAT,findmask INTEGER,H2_s INTEGER,H2_p INTEGER,\
CO_s INTEGER,CO_p INTEGER,CH4_s INTEGER,CH4_p INTEGER,C2H4_s INTEGER,C2H4_p INTEGER,\
C2H6_s INTEGER,C2H6_p INTEGER,C2H2_s INTEGER,C2H2_p INTEGER,\
H2_A FLOAT,CO_A FLOAT,CH4_A FLOAT,C2H4_A FLOAT,C2H6_A FLOAT,C2H2_A FLOAT,\
spzT_s FLOAT,spzT_e FLOAT,samT_s FLOAT,samT_e FLOAT,pres FLOAT,logTime TIMESTAMP)"


//获取数据库连接
//SQLITE_OPEN_READONLY, 
sqlite3 *db_get(int flags)
{
 sqlite3 *db;
 int rc;
 sqlite3_initialize();
 if((rc=sqlite3_open_v2(MY_DATABASE,&db,flags,NULL))!=SQLITE_OK) 
   {
	 if((rc=sqlite3_open_v2(MY_DATABASE,&db,flags,NULL))!=SQLITE_OK) 
 	   {
         printf("failed to open db\n");
         sqlite3_shutdown();
         return NULL;
	   }
   }
 return db;	
}

//关闭数据库连接 
void db_close(sqlite3 *db,sqlite3_stmt *stmt)
{
 if(stmt)
   sqlite3_finalize(stmt);
 if(db) 
   sqlite3_close(db);
 sqlite3_shutdown();	
}


//数据库执行sql查询 
sqlite3_stmt *db_query(sqlite3 *db,const char *sqlcmd)
{
 int rc;
 sqlite3_stmt *stmt;
 if((rc=sqlite3_prepare_v2(db,sqlcmd,strlen(sqlcmd),&stmt,NULL))!=SQLITE_OK) 
   {
     printf("failed to prepare:%s\n",sqlite3_errmsg(db));
     db_close(db,stmt);
     return NULL;
   }
 return stmt;	
}



int all_table_init(void)
{
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char sqlcmd[1024];
  int rc; 
  
  if((db=db_get(SQLITE_OPEN_READWRITE))==NULL)
    return -1;	
  strcpy(sqlcmd,CREATE_TBL_CMD);
  if((stmt=db_query(db,sqlcmd))==NULL)
    return -2;
  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
   {
      printf("all_table_init failed to step:%s\r\n",sqlite3_errmsg(db));
      db_close(db,stmt);
      return -3;	
   }
  db_close(db,stmt);
  return 0;		
}


int log_entry_add(char *str,int len)
{ 
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char sqlcmd[256];
  int rc;  
  
  if((str==NULL))
    return -1;
  
  if((db=db_get(SQLITE_OPEN_READWRITE))==NULL)
    return -2;

  //sprintf(sqlcmd,"INSERT INTO log_tbl VALUES(?,datetime())");
  sprintf(sqlcmd,"INSERT OR REPLACE INTO log_tbl VALUES(?,datetime())");
  if((stmt=db_query(db,sqlcmd))==NULL)
    return -3;
 
  if(len!=0)
    sqlite3_bind_text(stmt,1,str,len,NULL);
  else
    sqlite3_bind_text(stmt,1,str,strlen(str),NULL);

  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
   {
      printf("log_entry_add failed to step:%s\r\n",sqlite3_errmsg(db));
      db_close(db,stmt);
      return -4;	
   }
  db_close(db,stmt);
  return 0;	
}

int his_entry_add(ANALY_RESULT *str)
{ 
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char sqlcmd[256];
  int rc;  
  
  if((str==NULL))
    return -1;
  
  if((db=db_get(SQLITE_OPEN_READWRITE))==NULL)
    return -2;

  sprintf(sqlcmd,"INSERT OR REPLACE INTO his_tbl VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,datetime())");
  if((stmt=db_query(db,sqlcmd))==NULL)
    return -3;
  sqlite3_bind_double(stmt,1,(double)str->result.H2ppm);
  sqlite3_bind_double(stmt,2,(double)str->result.COppm);
  sqlite3_bind_double(stmt,3,(double)str->result.CH4ppm);
  sqlite3_bind_double(stmt,4,(double)str->result.C2H4ppm);
  sqlite3_bind_double(stmt,5,(double)str->result.C2H6ppm);
  sqlite3_bind_double(stmt,6,(double)str->result.C2H2ppm);
  sqlite3_bind_double(stmt,7,(double)str->result.TotHyd);
  sqlite3_bind_int(stmt,8,str->result.SmpTm);
  sqlite3_bind_double(stmt,9,(double)str->result.CO2ppm);
  sqlite3_bind_double(stmt,10,(double)str->result.Mst);
  sqlite3_bind_double(stmt,11,(double)str->result.temp);
  sqlite3_bind_int(stmt,12,str->find_mask);
  sqlite3_bind_int(stmt,13,str->pos_info[0].start);
  sqlite3_bind_int(stmt,14,str->pos_info[0].peak);
  sqlite3_bind_int(stmt,15,str->pos_info[1].start);
  sqlite3_bind_int(stmt,16,str->pos_info[1].peak);
  sqlite3_bind_int(stmt,17,str->pos_info[2].start);
  sqlite3_bind_int(stmt,18,str->pos_info[2].peak);
  sqlite3_bind_int(stmt,19,str->pos_info[3].start);
  sqlite3_bind_int(stmt,20,str->pos_info[3].peak);
  sqlite3_bind_int(stmt,21,str->pos_info[4].start);
  sqlite3_bind_int(stmt,22,str->pos_info[4].peak);
  sqlite3_bind_int(stmt,23,str->pos_info[5].start);
  sqlite3_bind_int(stmt,24,str->pos_info[5].peak);
  sqlite3_bind_double(stmt,25,(double)str->area[0]);
  sqlite3_bind_double(stmt,26,(double)str->area[1]);
  sqlite3_bind_double(stmt,27,(double)str->area[2]);
  sqlite3_bind_double(stmt,28,(double)str->area[3]);
  sqlite3_bind_double(stmt,29,(double)str->area[4]);
  sqlite3_bind_double(stmt,30,(double)str->area[5]);
  sqlite3_bind_double(stmt,31,(double)str->spzTemp1);
  sqlite3_bind_double(stmt,32,(double)str->spzTemp2);
  sqlite3_bind_double(stmt,33,(double)str->samTemp1);
  sqlite3_bind_double(stmt,34,(double)str->samTemp2);
  sqlite3_bind_double(stmt,35,(double)str->pres);
  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
   {
      printf("his_entry_add failed to step:%s\r\n",sqlite3_errmsg(db));
      db_close(db,stmt);
      return -4;	
   }
  db_close(db,stmt);
  return 0;	
}

int his_entry_read(ANALY_RESULT *str)
{ 
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char sqlcmd[256];
  int rc;  
  
  if((str==NULL))
    return -1;
  
  if((db=db_get(SQLITE_OPEN_READONLY))==NULL)
    return -2;

  sprintf(sqlcmd,"SELECT * FROM his_tbl order by SmpTm desc limit 1 offset 0");
  if((stmt=db_query(db,sqlcmd))==NULL)
    return -3;
  rc = sqlite3_step(stmt);
  if(rc==SQLITE_ROW)
   {
     str->result.H2ppm=(float)sqlite3_column_double(stmt,0);
	 str->result.COppm=(float)sqlite3_column_double(stmt,1);
	 str->result.CH4ppm=(float)sqlite3_column_double(stmt,2);
	 str->result.C2H4ppm=(float)sqlite3_column_double(stmt,3);
	 str->result.C2H6ppm=(float)sqlite3_column_double(stmt,4);
	 str->result.C2H2ppm=(float)sqlite3_column_double(stmt,5);
	 str->result.TotHyd=(float)sqlite3_column_double(stmt,6);
	 str->result.SmpTm=sqlite3_column_int(stmt,7);
	 str->result.CO2ppm=(float)sqlite3_column_double(stmt,8);
	 str->result.Mst=(float)sqlite3_column_double(stmt,9);
	 str->result.temp=(float)sqlite3_column_double(stmt,10);
	 str->find_mask=sqlite3_column_int(stmt,11);
	 str->pos_info[0].start=sqlite3_column_int(stmt,12);
	 str->pos_info[0].peak=sqlite3_column_int(stmt,13);
	 str->pos_info[1].start=sqlite3_column_int(stmt,14);
	 str->pos_info[1].peak=sqlite3_column_int(stmt,15);
	 str->pos_info[2].start=sqlite3_column_int(stmt,16);
	 str->pos_info[2].peak=sqlite3_column_int(stmt,17);
	 str->pos_info[3].start=sqlite3_column_int(stmt,18);
	 str->pos_info[3].peak=sqlite3_column_int(stmt,19);
	 str->pos_info[4].start=sqlite3_column_int(stmt,20);
	 str->pos_info[4].peak=sqlite3_column_int(stmt,21);
	 str->pos_info[5].start=sqlite3_column_int(stmt,22);
	 str->pos_info[5].peak=sqlite3_column_int(stmt,23);	 
	 str->area[0]=(float)sqlite3_column_double(stmt,24);
	 str->area[1]=(float)sqlite3_column_double(stmt,25);
	 str->area[2]=(float)sqlite3_column_double(stmt,26);
	 str->area[3]=(float)sqlite3_column_double(stmt,27);
	 str->area[4]=(float)sqlite3_column_double(stmt,28); 
     str->area[5]=(float)sqlite3_column_double(stmt,29);
	 str->spzTemp1=(float)sqlite3_column_double(stmt,30);
	 str->spzTemp2=(float)sqlite3_column_double(stmt,31);
	 str->samTemp1=(float)sqlite3_column_double(stmt,32);
	 str->samTemp2=(float)sqlite3_column_double(stmt,33); 
     str->pres=(float)sqlite3_column_double(stmt,34);    
     db_close(db,stmt);
     return 0;	
   }
  db_close(db,stmt);
  return -4;	
}
/*
char *database_format_time(time_t t)
{
static char time_str_arr[32];
//sprintf(time_str_arr,"%02d");	
strftime(char *ptr,size_t maxsize,const char *format,const struct tm *timeptr );
}*/
/*
世纪秒转日期字符串 
SELECT datetime(1377168853,'unixepoch','localtime');
日期字符串转世纪秒
SELECT strftime('%s','now');
*/
int data_entry_remove(char *tb_name,time_t rsv_time)
{ 
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char sqlcmd[256];
  int rc;  
  
  if((tb_name==NULL))
    return -1;
  
  if((db=db_get(SQLITE_OPEN_READWRITE))==NULL)
    return -2;

  sprintf(sqlcmd,"DELETE FROM %s where logTime<datetime(%d,'unixepoch','localtime')",tb_name,(unsigned int)(time(NULL)-rsv_time));
  if((stmt=db_query(db,sqlcmd))==NULL)
    return -3;

  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
   {
      printf("data_entry_remove failed to step:%s\r\n",sqlite3_errmsg(db));
      db_close(db,stmt);
      return -4;	
   }
  db_close(db,stmt);
  return 0;	
}