//
//  ApexMobileDB.m
//  Apex Mobile
//
//  Created by Ray on 14-2-28.
//  Copyright (c) 2014年 United Software Applications, Inc. All rights reserved.
//

#import "ApexMobileDB.h"

@implementation ApexMobileDB
+ (int) get_recordid:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause
{
    //    [ApexMobileDB execSql: [NSString stringWithFormat: @"update fields_info set abandon = 1 where user ='%@'",user] db:db];
    return [self get_recordid:db table:tablename where:whereclause order:@"_id"];
}

+ (int) get_recordid:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause order:(NSString*) orderby
{
    int ret = -1;
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id from %@ where %@ orderby %@",tablename,whereclause,orderby];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            ret = sqlite3_column_int(statement, 0);
            
            //            char *address = (char*)sqlite3_column_text(statement, 3);
            //            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
            
            
        }
        sqlite3_finalize(statement);
    }
    return ret;
    
}


+ (sqlite3*) get_db
{
    sqlite3* db = nil;
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *documents = [paths objectAtIndex:0];
    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    
    if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_open failed. msg:%s",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    return db;
    
}

+(void)execSql:(NSString *)sql db:(sqlite3 *)db
{
    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_exec failed msg:%s",sqlite3_errmsg(db));
        DebugLog(@"sqlite3_exec failed sql:%@",sql);
        sqlite3_close(db);
        
    }
}
+(BOOL)checkForField:(NSString *)table field:(NSString *)field db:(sqlite3 *)db
{
    
    sqlite3_stmt *stmt;
    NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)",table];
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            
            NSString *fieldName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
            if([field isEqualToString:fieldName])
            {
                sqlite3_finalize(stmt);
                return YES;
            }
        }
    }
    

    
    return NO;
}
+(NSArray*) get_Location
{
    sqlite3* db = [self get_db ];
    NSMutableArray* ret = [[NSMutableArray alloc] init];
    NSString *sqlQuery = @"select area,company,city,longitude,latitude,address,telephone,fax,contact,email from locations";
    sqlite3_stmt * statement;
    NSMutableDictionary* map = [[NSMutableDictionary alloc] init];
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString* area =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
            NSString* company =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
            NSString* city =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
            NSString* longitude =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];

            NSString* latitude =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];

            NSString* address =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
            NSString* telephone =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
            NSString* fax =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
            NSString* contact =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
            NSString* email =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];

            
            [map setValue:area forKey:@"area"];
            [map setValue:company forKey:@"company"];
            [map setValue:city forKey:@"city"];
            [map setValue:longitude forKey:@"longitude"];
            [map setValue:latitude forKey:@"latitude"];
            [map setValue:address forKey:@"address"];
            [map setValue:telephone forKey:@"telephone"];
            [map setValue:fax forKey:@"fax"];
            [map setValue:contact forKey:@"contact"];
            [map setValue:email forKey:@"email"];


            [ret addObject:map.copy];

            
            
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    return ret;
}
+(NSArray*) get_searchhistory:(NSString*) field
{
    sqlite3* db = [self get_db ];
    NSMutableArray* ret = [[NSMutableArray alloc] init];
    ApexMobileAppDelegate *appDelegate = [[UIApplication sharedApplication] delegate];
    NSString* user = appDelegate.user;
    NSString *sqlQuery = [NSString stringWithFormat:@"select h_val from search_history where h_field='%@' and user='%@' order by h_time desc",field,user];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            NSString *val = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
            [ret addObject:val];
            //            char *address = (char*)sqlite3_column_text(statement, 3);
            //            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
            
            
        }
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    
    return ret;
}
+(void) savehistory:(NSString*) field value:(NSString*) value
{
    sqlite3* db = [self get_db ];
    ApexMobileAppDelegate *appDelegate = [[UIApplication sharedApplication] delegate];
    NSString* user = appDelegate.user;
    int _id = [self get_recordid:db table:@"search_history" where:[NSString stringWithFormat:@"user='%@' and h_field='%@' and h_val ='%@'",user,field,value]];
    if(_id>=0)
    {
        NSString* sql = [NSString stringWithFormat:@"update search_history set h_time = datetime('now', 'localtime') where _id =%d",_id];
        [self execSql:sql db:db];
    }
    else
    {
        NSString* sql = [NSString stringWithFormat:@"insert into search_history(user,h_field,h_val) values('%@','%@','%@')",user,field,value];
        [self execSql:sql db:db];
    }
    
    
    sqlite3_close(db);
}
+ (BOOL) initializeDb {
    DebugLog (@"initializeDB");
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *documents = [paths objectAtIndex:0];
    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    
    
    // move db file from document to cache ;
    NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentFolderPath = [searchPaths objectAtIndex: 0];
    NSString* dbFilePath = [documentFolderPath stringByAppendingPathComponent:DBNAME];
    if ([[NSFileManager defaultManager] fileExistsAtPath: dbFilePath])
    {
        [[NSFileManager defaultManager] moveItemAtPath:dbFilePath toPath:database_path error:nil];
        
    }
    // end move;
    
    
    
    

    
    if (true)//! [[NSFileManager defaultManager] fileExistsAtPath: database_path])
    {
        sqlite3 *db = [self get_db];
//        int ret = sqlite3_close(db);
//db = [self get_db];
//        
//        ret = sqlite3_close(db);
        NSString *create_actions_info = @"CREATE TABLE IF NOT EXISTS actions_info (_id integer PRIMARY KEY,name varchar(20),function_name varchar(20),priority integer,abandon boolean,user varchar(20));";
        
        NSString *create_fields_info = @"CREATE TABLE IF NOT EXISTS fields_info (_id integer PRIMARY KEY,name varchar(20),aname varchar(20),field_type integer,function_name varchar(20),behavior integer,priority integer,show boolean,abandon boolean,user varchar(20));";
        
        NSString *create_search_history = @"CREATE TABLE IF NOT EXISTS search_history (_id INTEGER PRIMARY KEY,h_val VARCHAR(20),h_field VARCHAR(20),level INTEGER,h_time TIMESTAMP default (datetime('now', 'localtime')),user VARCHAR(20));";
        
        NSString* create_push_message =@"CREATE TABLE IF NOT EXISTS push_message (_id INTEGER PRIMARY KEY, s_id VARCHAR(20), e_id VARCHAR(20), msgcount INTEGER, message VARCHAR(20), h_time timestamp default (datetime('now', 'localtime')), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20), read BOOLEAN);";
        
		NSString* create_favorites =@"CREATE TABLE IF NOT EXISTS favorites (_id INTEGER PRIMARY KEY, name VARCHAR(20), params VARCHAR(20), action VARCHAR(20), module_name VARCHAR(20), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20));";
		NSString* create_history=@"CREATE TABLE IF NOT EXISTS history ( _id INTEGER PRIMARY KEY, name VARCHAR(20), params VARCHAR(20), action VARCHAR(20), module_name VARCHAR(20), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20));";
        
        
        NSString* create_location=@"CREATE TABLE IF NOT EXISTS locations ( _id INTEGER PRIMARY KEY, area VARCHAR(20), company VARCHAR(20), city VARCHAR(20), longitude VARCHAR(20), latitude VARCHAR(20) ,address VARCHAR(20),  telephone VARCHAR(20) , fax VARCHAR(20), contact VARCHAR(20), email VARCHAR(20));";
        
        
        
        
        //        CREATE TABLE users (_id integer PRIMARY KEY,name varchar(20),pass varchar(20));
        [self execSql:create_actions_info db:db];
        [self execSql:create_fields_info db:db];
        [self execSql:create_search_history db:db];
        [self execSql:create_push_message db:db];
        [self execSql:create_favorites db:db];
        [self execSql:create_history db:db];
        [self execSql:create_location db:db];
        
        
        if( ![self checkForField:@"search_history" field:@"level" db:db])
        {
            NSString* alter_search_history = @"ALTER TABLE search_history ADD level INTEGER";
            [self execSql:alter_search_history db:db];
        }
        NSTimeInterval time=[[NSDate date] timeIntervalSince1970];
        double t = time-2592000;      //NSTimeInterval返回的是double类型
        NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
        [formatter setDateFormat:@"yyyy-MM-dd"];

        NSString*timestr=[formatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:t]];
        
    //    [self execSql:[NSString stringWithFormat:@"delete from search_history where h_time<%@",timestr ] db:db];
        [self execSql:@"delete from search_history where julianday('now', 'localtime')-julianday(h_time, 'localtime')>30" db:db];
        int ret=sqlite3_close(db);
//        int aaa = 0;
    }
    return YES;
    
    
    DebugLog (@"bottom of initializeDb");
}


@end
