//
//  DBManager.m
//  SQLite3DBSample
//
//  Created by ドラッサル 亜嵐 on 2016/02/03.
//  Copyright © 2016年 ドラッサル 亜嵐. All rights reserved.
//

#import "DBManager.h"
#import <sqlite3.h>

@interface DBManager ()

@property (nonatomic, strong) NSString *documentsDirectory;
@property (nonatomic, strong) NSString *databaseFilename;
@property (nonatomic, strong) NSMutableArray *arrResults;


-(void)copyDatabaseIntoDocumentsDirectory;

-(void)runQuery:(const char *)query isQueryExecutable:(BOOL)queryExecutable;

@end

@implementation DBManager {
    NSLock* _lock;
}

+ (NSString*) createDatabaseIfRequiredAtPath:(NSString*)databasePath {
    
    if (databasePath == nil)
        return nil;
    
    
    //NSString *path = [NSString stringWithFormat:@"%@/%@", databasePath, kMainDBName];
    NSString *path = [NSString stringWithFormat:@"%@/%@", databasePath, DATABASE];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error = nil;
    
    if ([fileManager fileExistsAtPath:path] == NO)
    {
        // The writable database does not exist, so copy the default to the appropriate location.
        //NSString *defaultDBPath = [[NSBundle mainBundle] pathForResource:kMainDBName
        //                                                          ofType:nil];
        NSString *defaultDBPath = [[NSBundle mainBundle] pathForResource:DATABASE
                                                                  ofType:nil];
        BOOL success = [fileManager copyItemAtPath:defaultDBPath
                                            toPath:path
                                             error:&error];
        if (!success)
        {
            NSCAssert1(0, @"Failed to create writable database file with message '%@'.", [  error localizedDescription]);
            return nil;
        }
    }
    
    return path;
}

- (void)initFirst {
    NSLog(@"[DBManager] initFirst");

    // Set the documents directory path to the documentsDirectory property.
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    self.documentsDirectory = [paths objectAtIndex:0];
    
    //NSString *dbFilenameFixed = [DBManager createDatabaseIfRequiredAtPath:dbFilename];
    NSString *dbFilenameFixed = DATABASE;

    // Keep the database filename.
    self.databaseFilename = dbFilenameFixed;
    
    // Copy the database file into the documents directory if necessary.
    [self copyDatabaseIntoDocumentsDirectory];
    
    // Create a sqlite object.
    sqlite3 *sqlite3Database;
    
    // Set the database file path.
    NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];

    // Open the database.
    BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);
    if(openDatabaseResult == SQLITE_OK) {
        const char *sqlStatement =
        "CREATE TABLE IF NOT EXISTS FIRMWARE   (ID INTEGER PRIMARY KEY, FIRMWARE_UUID TEXT, FIRMWARE_DEVICE_TYPE TEXT, FIRMWARE_DEVICE_MODEL TEXT, FIRMWARE_VERSION TEXT, FIRMWARE_VERSION_STAMP TEXT, FIRMWARE_FILE TEXT);"
        "CREATE TABLE IF NOT EXISTS EEPROM_TEMPLATE   (ID INTEGER PRIMARY KEY, EEPROM_TEMPLATE_NAME TEXT, EEPROM_TEMPLATE_ID TEXT, EEPROM_TEMPLATE_MODEL TEXT, EEPROM_TEMPLATE_TYPE TEXT, EEPROM_TEMPLATE_OS TEXT, EEPROM_TEMPLATE_PIN TEXT);";
        
        char *error;
        
        if(sqlite3_exec(sqlite3Database, sqlStatement, NULL, NULL, &error) == SQLITE_OK){
            NSLog(@"All tables are created");
        } else {
            NSLog(@"Unable to create some table %s", error);
            sqlite3_free(error);
            error = NULL;
        }
    } else {
        NSLog(@"Database creation error");
    }
    
    /* BEGIN insert column if needed */
    NSString *query = @"PRAGMA table_info(EEPROM_TEMPLATE)";
    NSArray *result = [self loadDataFromDB:query];
    
    //NSLog(@"%@", result);
    
    bool columnFoundEEPROM_TEMPLATE_PIN = false;

    for(id key in result) {
        NSLog(@"%@", [key objectAtIndex:1]);

        if([[key objectAtIndex:1] isEqualToString:@"EEPROM_TEMPLATE_PIN"]) {
            columnFoundEEPROM_TEMPLATE_PIN = true;
        }
        
    }
    
    if(!columnFoundEEPROM_TEMPLATE_PIN) {
        const char *sqlStatement =
        "ALTER TABLE EEPROM_TEMPLATE ADD COLUMN EEPROM_TEMPLATE_PIN TEXT;";
        
        char *error;
        
        if(sqlite3_exec(sqlite3Database, sqlStatement, NULL, NULL, &error) == SQLITE_OK){
            NSLog(@"All tables are altered");
        } else {
            NSLog(@"Unable to alter some table %s", error);
            sqlite3_free(error);
            error = NULL;
        }
    }
    
    /* END insert column if needed */

    // Close the database.
    sqlite3_close(sqlite3Database);
}

-(void)copyDatabaseIntoDocumentsDirectory{
    // Check if the database file exists in the documents directory.
    NSString *destinationPath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
    if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {
        // The database file does not exist in the documents directory, so copy it from the main bundle now.
        NSString *sourcePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFilename];
        NSError *error;
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:destinationPath error:&error];
        
        // Check if any error occurred during copying and display it.
        if (error != nil) {
            NSLog(@"%@", [error localizedDescription]);
        }
    }
}

-(void)runQuery:(const char *)query isQueryExecutable:(BOOL)queryExecutable{
    // Create a sqlite object.
    sqlite3 *sqlite3Database;
    
    // Set the database file path.
    NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
    
    // Initialize the results array.
    if (self.arrResults != nil) {
        [self.arrResults removeAllObjects];
        self.arrResults = nil;
    }
    self.arrResults = [[NSMutableArray alloc] init];
    
    // Initialize the column names array.
    if (self.arrColumnNames != nil) {
        [self.arrColumnNames removeAllObjects];
        self.arrColumnNames = nil;
    }
    self.arrColumnNames = [[NSMutableArray alloc] init];
    
    
    // Open the database.
    BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);
    if(openDatabaseResult == SQLITE_OK) {
        // Declare a sqlite3_stmt object in which will be stored the query after having been compiled into a SQLite statement.
        sqlite3_stmt *compiledStatement;
        
        // Load all data from database to memory.
        BOOL prepareStatementResult = sqlite3_prepare_v2(sqlite3Database, query, -1, &compiledStatement, NULL);
        if(prepareStatementResult == SQLITE_OK) {
            // Check if the query is non-executable.
            if (!queryExecutable){
                // In this case data must be loaded from the database.
                
                // Declare an array to keep the data for each fetched row.
                NSMutableArray *arrDataRow;
                
                // Loop through the results and add them to the results array row by row.
                while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                    // Initialize the mutable array that will contain the data of a fetched row.
                    arrDataRow = [[NSMutableArray alloc] init];
                    
                    // Get the total number of columns.
                    int totalColumns = sqlite3_column_count(compiledStatement);
                    
                    // Go through all columns and fetch each column data.
                    for (int i=0; i<totalColumns; i++){
                        // Convert the column data to text (characters).
                        char *dbDataAsChars = (char *)sqlite3_column_text(compiledStatement, i);
                        
                        // If there are contents in the currenct column (field) then add them to the current row array.
                        if (dbDataAsChars != NULL) {
                            // Convert the characters to string.
                            [arrDataRow addObject:[NSString  stringWithUTF8String:dbDataAsChars]];
                        } else {
                            [arrDataRow addObject:@""];
                        }
                        
                        // Keep the current column name.
                        if (self.arrColumnNames.count != totalColumns) {
                            dbDataAsChars = (char *)sqlite3_column_name(compiledStatement, i);
                            [self.arrColumnNames addObject:[NSString stringWithUTF8String:dbDataAsChars]];
                        }
                    }
                    
                    // Store each fetched data row in the results array, but first check if there is actually data.
                    if (arrDataRow.count > 0) {
                        [self.arrResults addObject:arrDataRow];
                    }
                }
            }
            else {
                // This is the case of an executable query (insert, update, ...).
                
                // Execute the query.
                int executeQueryResults = sqlite3_step(compiledStatement);
                if (executeQueryResults == SQLITE_DONE) {
                    // Keep the affected rows.
                    self.affectedRows = sqlite3_changes(sqlite3Database);
                    
                    // Keep the last inserted row ID.
                    self.lastInsertedRowID = sqlite3_last_insert_rowid(sqlite3Database);
                }
                else {
                    // If could not execute the query show the error message on the debugger.
                    NSLog(@"DB Error: %s", sqlite3_errmsg(sqlite3Database));
                }
            }
        }
        else {
            // In the database cannot be opened then show the error message on the debugger.
            NSLog(@"%s", sqlite3_errmsg(sqlite3Database));
        }
        
        // Release the compiled statement from memory.
        sqlite3_finalize(compiledStatement);
        
        // Close the database.
        sqlite3_close(sqlite3Database);
    }
}

-(NSArray *)loadDataFromDB:(NSString *)query{
    // Run the query and indicate that is not executable.
    // The query string is converted to a char* object.
    [self runQuery:[query UTF8String] isQueryExecutable:NO];
    
    // Returned the loaded results.
    return (NSArray *)self.arrResults;
}

-(void)executeQuery:(NSString *)query{
    // Run the query and indicate that is executable.
    [self runQuery:[query UTF8String] isQueryExecutable:YES];
}

- (NSArray*)getFirmwareDataList {
    NSString *query = @"SELECT ID, FIRMWARE_UUID, FIRMWARE_DEVICE_TYPE, FIRMWARE_DEVICE_MODEL, FIRMWARE_VERSION, FIRMWARE_VERSION_STAMP, FIRMWARE_FILE FROM FIRMWARE";
    NSArray *result = [self loadDataFromDB:query];
    return result;
}

- (NSArray*)getEepromTemplateData {
    NSString *query = @"SELECT ID, EEPROM_TEMPLATE_NAME, EEPROM_TEMPLATE_ID, EEPROM_TEMPLATE_MODEL, EEPROM_TEMPLATE_TYPE, EEPROM_TEMPLATE_OS, EEPROM_TEMPLATE_PIN FROM EEPROM_TEMPLATE";
    NSArray *result = [self loadDataFromDB:query];
    return result;
}

- (bool)saveFirmwareData:(NSString *)uuid
              deviceType:(NSString *)deviceType
             deviceModel:(NSString *)deviceModel
                 version:(NSString *)version
            versionStamp:(NSString *)versionStamp
                    file:(NSString *)file
{
    NSString *queryCheck = [NSString stringWithFormat:@"SELECT FIRMWARE_UUID FROM FIRMWARE WHERE FIRMWARE_UUID = '%@'",uuid];
    NSArray *queryCheckResult = [self loadDataFromDB:queryCheck];
    if([queryCheckResult count] != 0) {
        NSString *queryUpdate = [NSString stringWithFormat:@"UPDATE FIRMWARE SET FIRMWARE_DEVICE_MODEL = '%@', FIRMWARE_DEVICE_TYPE = '%@', FIRMWARE_VERSION = '%@', FIRMWARE_VERSION_STAMP = '%@', FIRMWARE_FILE = '%@' WHERE FIRMWARE_UUID = '%@'",deviceType,deviceModel,version,versionStamp,file,uuid];
        [self executeQuery:queryUpdate];
    } else {
        NSString *queryInsert = [NSString stringWithFormat:@"INSERT INTO FIRMWARE (FIRMWARE_UUID, FIRMWARE_DEVICE_TYPE, FIRMWARE_DEVICE_MODEL, FIRMWARE_VERSION, FIRMWARE_VERSION_STAMP, FIRMWARE_FILE) VALUES ('%@','%@','%@','%@','%@','%@')",uuid, deviceType, deviceModel, version, versionStamp, file];
        [self executeQuery:queryInsert];
    }
    return true;
}

- (bool)saveEepromTemplateData:(NSString *)recordId
                    recordName:(NSString *)recordName
                        dataId:(NSString *)dataId
                   deviceModel:(NSString *)dataModel
                      dataType:(NSString *)dataType
                        dataOs:(NSString *)dataOs
                        dataPin:(NSString *)dataPin
{
    NSString *queryCheck = [NSString stringWithFormat:@"SELECT ID FROM EEPROM_TEMPLATE WHERE ID = '%@'",recordId];
    NSArray *queryCheckResult = [self loadDataFromDB:queryCheck];
    if([queryCheckResult count] != 0) {
        NSString *queryUpdate = [NSString stringWithFormat:@"UPDATE EEPROM_TEMPLATE SET EEPROM_TEMPLATE_NAME = '%@', EEPROM_TEMPLATE_ID = '%@', EEPROM_TEMPLATE_MODEL = '%@', EEPROM_TEMPLATE_TYPE = '%@', EEPROM_TEMPLATE_OS = '%@', EEPROM_TEMPLATE_PIN = '%@', WHERE ID = '%@'",recordName, dataId, dataModel, dataType, dataOs, dataPin, recordId];
        [self executeQuery:queryUpdate];
    } else {
        NSString *queryInsert = [NSString stringWithFormat:@"INSERT INTO EEPROM_TEMPLATE (EEPROM_TEMPLATE_NAME, EEPROM_TEMPLATE_ID, EEPROM_TEMPLATE_MODEL, EEPROM_TEMPLATE_TYPE, EEPROM_TEMPLATE_OS, EEPROM_TEMPLATE_PIN) VALUES ('%@','%@','%@','%@','%@','%@')",recordName, dataId, dataModel, dataType, dataOs, dataPin];
        [self executeQuery:queryInsert];
    }
    return true;
}

- (bool)deleteFirmwareData:(NSString *)recordId {
    NSString *queryDelete = [NSString stringWithFormat:@"DELETE FROM FIRMWARE WHERE ID = '%@'",recordId];
    [self executeQuery:queryDelete];
    return true;
}

- (bool)deleteEepromTemplateData:(NSString *)recordId {
    NSString *queryDelete = [NSString stringWithFormat:@"DELETE FROM EEPROM_TEMPLATE WHERE ID = '%@'",recordId];
    [self executeQuery:queryDelete];
    return true;
}

- (BOOL)checkForField:(NSString *)tblName colName:(NSString *)colName {
    // Create a sqlite object.
    sqlite3 *sqlite3Database;
    
    // Set the database file path.
    NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
    
    // Open the database.
    BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);
    if(openDatabaseResult == SQLITE_OK) {
        
        NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)", tblName];
        sqlite3_stmt *stmt;
        
        if (sqlite3_prepare_v2(sqlite3Database, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK)
        {
            return NO;
        }
        
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSString *fieldName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
            if([colName isEqualToString:fieldName]) {
                // Close the database.
                sqlite3_close(sqlite3Database);
                return YES;
            }
        }
        // Close the database.
        sqlite3_close(sqlite3Database);
    }
    return NO;
}

/*
#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation
- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender {
    // Get the new view controller using [segue destinationViewController].
    // Pass the selected object to the new view controller.
}
*/

# pragma mark - Singleton pattern

static DBManager *_sharedInstance;

- (instancetype)init {
    @throw [NSException exceptionWithName:NSInternalInconsistencyException reason:@"[Operation] Use 'sharedInstance' instead of 'init' as this class is singleton." userInfo:nil];
}

+ (instancetype)sharedInstance {
    @synchronized(self) {
        if (_sharedInstance == nil) {
            (void) [[self alloc] initPrivate]; // ここでは代入していない
        }
    }
    return _sharedInstance;
}

- (instancetype)initPrivate {
    self = [super init];
    if (self) {
        // 初期処理
        _lock = [[NSLock alloc] init];
        [self initFirst];
    }
    return self;
}

+ (id)allocWithZone:(NSZone *)zone {
    @synchronized(self) {
        if (_sharedInstance == nil) {
            _sharedInstance = [super allocWithZone:zone];
            return _sharedInstance;  // 最初の割り当てで代入し、返す
        }
    }
    return nil;
}

- (id)copyWithZone:(NSZone *)zone {
    return self;
}

@end