#include "DST_DB.h"
#include "sqlite3.h"
#include "DST_CommonAPI.h"
#include "DST_ChannelTune.h"
#include "DST_HostInterface.h"
#include "DST_DB_Engine.h"


void DST_GetAVState(DS_U8 *RF, DS_U16 *SourceID, DS_U8 *Mode, DS_U8 *State);
int CT_DB_GetTSID(DS_U8 RF);
DS_U32* DST_UTF82Uni(DS_U8 *utf);
void CT_DB_GetVideoPid(DS_U8 RF, DS_U16 sID,DS_U16* pcr, DS_U16* pid, DS_U8* type);
void CT_ChMapUpdate();
int CT_ChMapCount();

#ifdef SQLITE_SYSTEM_MALLOC
static void sqlite3MemSetDefault(void);
#endif

static SkipList g_skip[1000];
static int g_skip_count = 0;

SkipList* DST_DB_GetSkipChannel(int *size)
{
	*size = g_skip_count;
	return g_skip;

}
int DST_DB_GetSkipChannelCount()
{
	return g_skip_count;
}

void DST_DB_MakeSkipChannel()
{
	CDB db;
	db.GetTable("select rf, program_number from skip_list");
	g_skip_count = db.GetRow();
	
	memset(g_skip,0,sizeof(SkipList)*(g_skip_count));
	if(g_skip_count < 1) return;	
	for(int i=0;i<(g_skip_count);i++)
	{
		g_skip[i].rf= atoi(db.GetResult( (i+1)*db.GetCol() + 0  ));
		g_skip[i].program_number= atoi(db.GetResult( (i+1)*db.GetCol() + 1  ));
	}
}

void DST_DB_SetSkipChannel(int rf, int program_number, bool bAdd)
{
	CDB db;
	if(bAdd) db.Query("INSERT OR REPLACE INTO skip_list  VALUES (%d, %d)",rf, program_number);
	else db.Query("delete from skip_list where rf=%d and program_number=%d",rf, program_number);
	CT_ChMapUpdate(); // Skip 리스트에 따른 채널 업다운 목록을 갱신한다.
}

bool DST_DB_PresentChMap()
{
	return (CT_ChMapCount() > 0);
}

int DST_DB_GetMajor(DS_U8 RF, DS_U16 program_number)
{
	CDB db;
	db.GetTable("select major from channel_db where rf=%d and program_number=%d", RF, program_number);
	return (db.GetRow() < 1) ? 0 : atoi(db.GetResult(1));
}

int DST_DB_GetMinor(DS_U8 RF, DS_U16 program_number)
{
	CDB db;
	db.GetTable("select minor from channel_db where rf=%d and program_number=%d", RF, program_number);
	return (db.GetRow() < 1) ? 0 : atoi(db.GetResult(1));
}

void DST_DB_ChannelUpDn(bool bUp)
{
	T();
	CDB db;
	int rf = DST_EEPROM_GetRF();
	int major = rf;
	int minor = 1;
	int program_number = DST_EEPROM_GetProgramNumber();
	
	db.GetTable("select major,minor from channel_updn where rf =%d and program_number=%d", rf, program_number);
	if (db.GetRow()) 
	{
		major = atoi(db.GetResult(2));
		minor = atoi(db.GetResult(3));
	}
	
	if (bUp)
	{
		// 바로 위 채널 찾아보자
		db.GetTable("select rf, program_number from channel_updn where " 
			"(major > %d) or (major >=%d and minor > %d) or (major>=%d and minor >=%d and rf > %d) "
			"order by major, minor, rf limit 1",
			major, major, minor, major, minor, rf);
		if (db.GetRow() == 0) // 밑에서 첫번째 채널 찾아보자
		{
			db.GetTable("select rf, program_number from channel_updn order by major, minor, rf limit 1");
		} 
	}
	else
	{
		// 바로 아래  채널 찾아보자
		db.GetTable("select rf, program_number from channel_updn where " 
			"(major < %d) or (major <=%d and minor < %d) or (major<=%d and minor <=%d and rf < %d) "
			"order by major desc, minor desc, rf desc limit 1",
			major, major, minor, major, minor, rf);
		if (db.GetRow() == 0) // 위에서 첫번째 채널 찾아보자
		{
			db.GetTable("select rf, program_number from channel_updn order by major desc, minor desc, rf desc limit 1");
		} 
	}
	if (db.GetRow() > 0) DST_UI_ChannelTune(atoi(db.GetResult(2)), atoi(db.GetResult(3)));
}

ChannelMap* DST_DB_GetChannelMapForChEdit(int *nChannels)
{
	CDB db;
	db.GetTable("select rf, program_number, major, minor, name from channel_db order by major,minor,rf");
	
	*nChannels = db.GetRow();
	if(*nChannels < 1) return 0;
	ChannelMap* chMap = (ChannelMap *)DST_OS_Malloc(sizeof(ChannelMap) * (*nChannels));
	memset(chMap, 0, sizeof(ChannelMap) * (*nChannels));
	for(int i=0;i<(*nChannels);i++)
	{
		chMap[i].rf = atoi(db.GetResult( (i+1)*db.GetCol() ));
		if(atoi(db.GetResult( (i+1)*db.GetCol()+3 )) == 0)
			sprintf(chMap[i].num, "%d", atoi(db.GetResult( (i+1)*db.GetCol()+2 )));
		else
			sprintf(chMap[i].num, "%d-%d", atoi(db.GetResult( (i+1)*db.GetCol()+2 )), atoi(db.GetResult( (i+1)*db.GetCol()+3 )));
		chMap[i].number = atoi(db.GetResult( (i+1)*db.GetCol()+1 ));
		strcpy((char*)chMap[i].name, db.GetResult((i+1)*db.GetCol()+4));
	}
	return chMap;
}

int DST_DB_GetChannelCount()
{
	return CT_ChMapCount();
}

bool DST_DB_GetCurrentChannelInfo(char* chNum, DS_U32* chName, int *num)
{
	if (chNum) chNum[0] = 0;
	if (chName) chName[0] = 0;

#if 0		
	DS_U8 RF= DST_EEPROM_GetRF();
	DS_U16 program_number = DST_EEPROM_GetProgramNumber();
#else
	DS_U8  RF= 0;
	DS_U16 program_number = 0;
	DST_GetAVState(&RF, &program_number);
#endif
	
	if (program_number == 0)
	{
		if (num) *num = DST_GetFrequencyNumberbyIndex(RF);
		if (chNum) sprintf(chNum,"%d", DST_GetFrequencyNumberbyIndex(RF));
		return false;
	}
	CDB db;
	db.GetTable("select major, minor, name from channel_db where rf =%d and program_number=%d", RF, program_number);
	if (db.GetRow() < 1)
	{
		if (num) *num = DST_GetFrequencyNumberbyIndex(RF);
		if (chNum) sprintf(chNum,"%d", DST_GetFrequencyNumberbyIndex(RF));
		return false;
	}
	
	int major = atoi(db.GetResult(3));
	int minor = atoi(db.GetResult(4));
	if (chName)
	{
		DS_U32 *strText32 = DST_UTF82Uni((DS_U8*)db.GetResult(5)); // 호출한 쪽에서 메모리 해제	
		if (strText32) strcpy32(chName, strText32);
	}
	if (minor == 0)
	{
		sprintf(chNum,"%d (%d) ", major, DST_GetFrequencyNumberbyIndex(RF));
	}
	else
	{
		sprintf(chNum,"%d-%d(%d) ", major, minor, DST_GetFrequencyNumberbyIndex(RF));
	}
	return true;
}

// 부팅 후 혹은 오토 스캔 후 호출 된다.
void DST_DB_TuneFirstChannel()
{
	if (DST_DB_PresentChMap() == false)
	{
		DST_UI_ChannelTune(DST_MinRF(), 0);
		return;
	}
	CDB db;
	// RF 번호와 프로그램넘버 일치하는 채널을 찾는다.
	int rf = DST_EEPROM_GetRF();
	int program_number = DST_EEPROM_GetProgramNumber();
	db.GetTable("select rf, program_number from channel_db where rf =%d and program_number=%d", rf,  program_number);
	if (db.GetRow()) 
		{
		DST_UI_ChannelTune(atoi(db.GetResult(2)), atoi(db.GetResult(3)));
			return;
		}
	// RF 번호 일치하는 채널을 찾는다.
	db.GetTable("select rf, program_number from channel_db where rf =%d", rf);
	if (db.GetRow()) 
		{
		DST_UI_ChannelTune(atoi(db.GetResult(2)), atoi(db.GetResult(3)));
			return;
		}
	// 첫번째 채널
	db.GetTable("select rf, program_number from channel_db");
	if (db.GetRow()) 
	{
		DST_UI_ChannelTune(atoi(db.GetResult(2)), atoi(db.GetResult(3)));
		return;
	}
	DST_UI_ChannelTune(DST_MinRF(), 0);
}

int DST_DB_GetProgramNumber(int major, int minor)
{
	CDB db;
	int program_number = 0;
	db.GetTable("select program_number from channel_db where major =%d and minor=%d order by major,minor,rf", major,  minor);
	if(db.GetRow() < 1)
	{	
		db.GetTable("select program_number from channel_db where major =%d order by major,minor,rf", major);
	}
		
	if (db.GetRow() > 0) program_number = atoi(db.GetResult(1));

	return program_number;
}

int DST_DB_GetProgramNumber(int rf)
{
	CDB db;
	db.GetTable("select program_number from channel_db where rf =%d order by minor", rf);
	return (db.GetRow() > 0) ?  atoi(db.GetResult(1)) : 0;	
}

void DST_DB_Del()
{
	CDB db,db1;
	db.GetTable("select tbl_name from sqlite_master where type='table'");
	for (int i= 0; i < db.GetRow(); i++) 
	{
		db1.Query("delete from %s", db.GetResult(i+1));
	}
}

void DST_DB_Del(DS_U8 RF)
{
	CDB db,db1;
	db.GetTable("select tbl_name from sqlite_master where type='table'");
	for (int i= 0; i < db.GetRow(); i++)
	{
		db1.GetTable("pragma table_info(%s)", db.GetResult(i+1));
		bool bFind = false;
		for (int j= 0; j < db1.GetRow(); j++)
		{
			// rf라는 이름을 가진 field가 있는 테이블의 내용을 모두 지운다.
			if (strcmp("rf", db1.GetResult((j+1)*db1.GetCol()+1))) continue;
			bFind = true;
			break;
		}
		if (bFind) db1.Query("delete from %s where rf = %d", db.GetResult(i+1), RF);
	}
}

// FLASH 메모리 저장

static DS_U32 DST_CRC32(DS_U8 *data, int len)
{
	static DS_U32 crcTable[256];
	static bool bFirst=true;
	if (bFirst)
	{
		bFirst = false;
		for (int i=0; i<256; i++) {
			DS_U32 crc = 0;
			for (int j=7; j>=0; j--) {
				if (((i >> j) ^ (crc >> 31)) & 1) crc=(crc<<1)^0x04C11DB7;
				else crc<<=1;
			}
			crcTable[i] = crc;
		}
	}
	DS_U32 crc = 0xFFFFFFFF;
	for (int i = 0; i < len; ++i) crc = (crc << 8) ^ crcTable[(crc >> 24) ^ (*data++)];
	return crc;
}

// FLASH MEMORY LOW LEVEL ACCESS FUNCTION
static char *strFlash = 0; // [FLASH_DB_SIZE];
#define FLASH_DB_ADDR0 FLASH_CH_MAP_POS
#define FLASH_DB_ADDR1 (FLASH_CH_MAP_POS + FLASH_DB_SIZE)

static void DST_Flash_Read(int bank)
{
	if (strFlash) DST_OS_Free(&strFlash);
	strFlash = (char*)DHL_Flash_Read(bank ?  FLASH_DB_ADDR1 : FLASH_DB_ADDR0, FLASH_DB_SIZE);
}

static void DST_Flash_Write()
{
	DHL_Flash_Write(FLASH_DB_ADDR0, strlen(strFlash)+1, (DS_U8*)strFlash);
	DHL_Flash_Write(FLASH_DB_ADDR1, strlen(strFlash)+1, (DS_U8*)strFlash);
}

class CString
{
private:
	char* strText;
public:
	CString()
	{
		strText = (char*)DST_OS_Calloc(1,1);
	}
	~CString()
	{
		DST_OS_Free(&strText);
	}
	int Add(const char* strSQL, ...)
	{
		if (strSQL == 0) return 0;
		va_list ap;
	  	char *z;
		va_start(ap, strSQL);
	  	z = sqlite3_vmprintf(strSQL, ap);
	  	va_end(ap);
		int nLen = strlen(strText) + strlen(z) + 1;
		char* strTmp = (char*)DST_OS_Calloc(nLen,1);
		sprintf(strTmp, "%s%s",strText, z);
		DST_OS_Free(&strText);
		sqlite3_free(z);
		strText = strTmp;	
		return nLen;
	}
	char* Get()
	{
		return strText;
	}
};

static void DST_DB_TableToString(const char* strTableName, CString &strQ)
{
	CDB db;
	CDB db_type;
	db.GetTable("select sql from sqlite_master where name=%Q", strTableName);
	if (db.GetRow() < 1) return;
	
	strQ.Add("drop table if exists %s;\n%s;\n", strTableName, db.GetResult(1)); // 생성 쿼리
	
	db.GetTable("select * from %s", strTableName);
	// 필드가 integer 형인지 text 형인지 알기 위해서 쿼리문을 만든다.
	if (db.GetRow() < 1) return;
	CString strT;
	strT.Add("select ");
	for (int i = 0; i < db.GetCol(); i++) strT.Add("typeof(%s)%c", db.GetResult(i), (i < db.GetCol()-1) ? ',' : ' ');
	strT.Add(" from %s", strTableName);
	//DST_Printf("%s\n", strT);
	db_type.GetTable(strT.Get());
	// 각 라인별로 쿼리를 추출한다.
	for (int i = 0; i < db.GetRow(); i++)
	{
		strQ.Add("insert into %s values(", strTableName);
		for (int j = 0; j < db.GetCol(); j++)
		{
			char* strData = db.GetResult((i+1)*db.GetCol() + j);
			bool IsText = (db_type.GetResult(j + db.GetCol())[0] == 't');
			strQ.Add(IsText ? "%Q%c" : "%s%c", strData, (j < db.GetCol()-1) ? ',' : ' ');
		}
		strQ.Add(");\n");
	}
	//DST_Printf("%s\n", strQ);
}

static void DST_DB_QueryFile_Save()
{
	CString strQ;
	DST_DB_TableToString("pat", strQ);
	DST_DB_TableToString("pmt", strQ);
//	DST_DB_TableToString("signal", strQ);
	DST_DB_TableToString("config", strQ);
	DST_DB_TableToString("skip_list", strQ);
	DST_DB_TableToString("tvct", strQ);
	DST_DB_TableToString("tvct_sub", strQ);
	char *strData = strQ.Get();
	//DST_Printf("\n%s\n", strData);
	
	CString strH;
	strH.Add("VERSION %d\nLENGTH %d\nCRC %d\n", FLASH_VERSION, strlen(strData)+1, (int)DST_CRC32((DS_U8*)strData, strlen(strData)+1));
	strH.Add("-------------------------------------------------------------------------------------------------");
	char *strHead = strH.Get();
	strHead[63] = '\n';
	
	if (memcmp(strFlash, strHead, 64) == 0 && memcmp(&strFlash[64], strData, strlen(strData)+1) == 0) return; // 이미 저장된 데이터와 같다.
	memcpy(strFlash, strHead, 64);
	memcpy(&strFlash[64], strData, strlen(strData)+1);
	DST_Flash_Write();
}

static bool DST_DB_QueryFile_Load(int bank)
{
	// Flash memory에서 읽어온다.
	DST_Flash_Read(bank);
	int nLen = 0;
	for (int i = 0; i< FLASH_DB_SIZE; i++)
	{
		if (strFlash[i] != 0) continue;
		nLen = i;
		break;
	}
	T();
	if (nLen== 0) return false;
	T();
	if (memcmp("VERSION", strFlash, strlen("VERSION")) != 0) return false;
	T();
	char strName[6][20];
	memset(strName, 0, sizeof(strName));
	int nStart = 0;
	int nPos = 0;
	int nstrFlashLen = (int)strlen(strFlash);
	for (int i = 0; i < nstrFlashLen; i++)
	{
		if (strFlash[i] == ' ' || strFlash[i] == '\n')
		{
			memcpy(strName[nPos],  &strFlash[nStart], i-nStart);
			strName[nPos][i-nStart] = 0;
			nPos++;
			nStart = i+1;
			if (nPos >= 6) break; 
		}
		if (i-nStart >= 19) break;
	}
	//char strName[3][20];
	//char strValue[3][20];
	//sscanf(strFlash, "%s%s%s%s%s%s", strName[0], strValue[0],strName[1], strValue[1],strName[2], strValue[2]);
	DST_Printf("strName[0] = %s\n", strName[0]);
	DST_Printf("strValue[0] = %s\n", strName[1]);
	DST_Printf("strName[1] = %s\n", strName[2]);
	DST_Printf("strValue[1] = %s\n", strName[3]);
	DST_Printf("strName[2] = %s\n", strName[4]);
	DST_Printf("strValue[2] = %s\n", strName[5]);
	int version = atoi(strName[1]);
	nLen = atoi(strName[3]);
	int crc1 = atoi(strName[5]);
	
	if (version!= FLASH_VERSION) return false;
	T();
	if (nLen == 0 || nLen >= FLASH_DB_SIZE) return false; 
	int crc2 =DST_CRC32((DS_U8*)&strFlash[64], nLen);
	T();
	DST_Printf("crc1= %d crc2=%d\n", crc1, crc2);
	if (crc1 != crc2) return false;
	T();
	return true;
	
}

static DS_U32 g_sync_request_time = 0;
static DS_U32 g_Sync_finish_time = 0;

void DST_DB_Sync()
{
	g_sync_request_time = DST_OS_GetTickCount();
}

bool DST_DB_IsSync()
{
	return (g_Sync_finish_time < g_sync_request_time) ? false : true;
}

static void tDB_Sync()
{
	while (1)
	{
		DST_OS_Delay(DST_OS_GetTicksPerSecond());
		if (DST_DB_IsSync()) continue;
		T();
		DST_DB_QueryFile_Save();
		T();
		g_Sync_finish_time = DST_OS_GetTickCount();
	}
}

extern sqlite3 *g_db;
void DST_DB_Open()
{
	if (g_db) return;
#ifdef SQLITE_SYSTEM_MALLOC
	sqlite3MemSetDefault();
#endif
#ifdef SQLITE_OS_OTHER
	sqlite3_open((char*)":memory:", &g_db);
#else
	sqlite3_open((char*)"tmp_isdbt.db", &g_db);
#endif
#ifdef SQLITE_ENABLE_MEMSYS5
	void *pBuf = DST_OS_Malloc(1024*1024);   
	sqlite3_config(SQLITE_CONFIG_HEAP, pBuf, 1024*1024, 4);
#endif	
	if (g_db == 0)
	{
		DST_Printf("%s|%d|Can not open database try\n", __func__, __LINE__);
	}
	CDB db;
//	db.Query("create table if not exists signal (rf int, signal_strength int, bera int, berb int, block int)");
	db.Query("create table if not exists pat (rf int, program_number int, pid int, crc int)");
	db.Query("create table if not exists pmt(rf int, program_number int, pcr_pid int, ca_pid int, video_pid int, video_type int, minor int, crc int)");
	db.Query("create table if not exists eit(rf int, id int, source_id int, version_number int, crc int)");
	db.Query("create table if not exists eit_sub(rf int, id int, source_id int, event_id int, start_time int, length_in_seconds int, title text)");
	db.Query("create table if not exists ett(rf int, source_id int, event_id int,  title text, crc int)");
	db.Query("create table if not exists skip_list(rf int, program_number int, primary key(rf, program_number))");

	db.Query("create table if not exists cvct(rf int unique, transport_stream_id int, version_number int, crc32)");	
 	db.Query("create table if not exists cvct_sub("
 		"rf int, short_name text, long_name text, major_channel_number int, minor_channel_number int, "
 		"modulation_mode int, carrier_frequency int, channel_TSID int, program_number int, ETM_location int, "
 		"access_controlled int, hidden int, show_guide int, service_type int, source_id int)");
 
 	db.Query("create table if not exists tvct(rf int unique, transport_stream_id int, version_number int, crc32)");	
 	db.Query("create table if not exists tvct_sub("
 		"rf int, short_name text, long_name text, major_channel_number int, minor_channel_number int, "
 		"modulation_mode int, carrier_frequency int, channel_TSID int, program_number int, ETM_location int, "
 		"access_controlled int, hidden int, show_guide int,  service_type int, source_id int)");
 					
	bool bValidDB = false;
	if (DST_DB_QueryFile_Load(0) == true)  bValidDB = true;
	if (bValidDB == false) if (DST_DB_QueryFile_Load(1) == true) bValidDB = true;
	if (bValidDB == true)
	{
		DST_Printf("%s", &strFlash[64]);
		T();
		db.Query(&strFlash[64]);
		T();
		// CT_ChMapUpdate();
		T();
		DST_DB_MakeSkipChannel();
	}
	DST_OS_SpawnTask( (void(*) (void *))tDB_Sync, (char*)"tDB_Sync", APP_TASK_PRIO_EEPROM_SYNC, WIN_MGR_TASK_STACKSIZE,  0);
}

void DST_DB_Close()
{
	if(g_db) sqlite3_close(g_db);
	g_db = 0;
}

int DST_DB_GetRF(int major, int minor, bool* bFound)
{
	CDB db;
	db.GetTable("select rf from channel_db where major=%d and minor=%d order by major,minor,rf",major, minor);
	if(db.GetRow() < 1)
	{	
		db.GetTable("select rf from channel_db where major=%d  order by major,minor,rf",major);
	}
	if(db.GetRow() < 1)
	{
		*bFound = false;
		return 0;
	}
	
	return atoi(db.GetResult(1));
}
int DST_DB_AvailableChannelCount(int major)
{
	CDB db;
	db.GetTable("select major from channel_db where ( major=%d or (major >= %d and major < %d))  ", major,major*10,major*10+9);
	
	return db.GetRow();

}

bool DST_DB_PresentMatchChannel(int rf, int major, int minor)
{
	CDB db;
	if(minor == 0)
		db.GetTable("select * from channel_db where rf=%d and major=%d ", rf, major );
	else
		db.GetTable("select * from channel_db where rf=%d and major=%d and minor=%d ", rf, major, minor );
	DST_Printf("rf=%d and major=%d and minor=%d\n", rf, major, minor);
	return (db.GetRow() < 1 ? false: true);
}

#ifdef SQLITE_SYSTEM_MALLOC

static int sqlite3MemRoundup(int n)
{
  return ((n+7) & (~7));
}

static int sqlite3MemInit(void *)
{
  return SQLITE_OK;
}

static void sqlite3MemShutdown(void *)
{
  return;
}

static void sqlite3MemSetDefault(void)
{
  static const sqlite3_mem_methods defaultMethods = {
     DST_OS_MallocDirect,
     DST_OS_FreeDirect,
     DST_OS_ReallocDirect,
     DST_OS_MemSize,
     sqlite3MemRoundup,
     sqlite3MemInit,
     sqlite3MemShutdown,
     0
  };
  sqlite3_config(SQLITE_CONFIG_MALLOC, &defaultMethods);
}

#endif /* SQLITE_SYSTEM_MALLOC */