//
//  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 VIDEO      (ID INTEGER PRIMARY KEY, VIDEO_ID TEXT, VIDEO_FILENAME TEXT, VIDEO_ITAG TEXT, VIDEO_TITLE TEXT, VIDEO_DESCRIPTION TEXT, VIDEO_DURATION TEXT, VIDEO_CHANNELID TEXT, VIDEO_CHANNELTITLE TEXT, VIDEO_THUMB, VIDEO_PUBLISHDATE TEXT, VIDEO_RAW TEXT);"
        "CREATE TABLE IF NOT EXISTS CATEGORY   (ID INTEGER PRIMARY KEY, NAME 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");
    }
    
    // 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*)getVideoDataList {
    NSString *query = @"SELECT ID, VIDEO_ID, VIDEO_FILENAME, VIDEO_ITAG, VIDEO_TITLE, VIDEO_DESCRIPTION, VIDEO_DURATION, VIDEO_CHANNELID, VIDEO_CHANNELTITLE, VIDEO_THUMB, VIDEO_PUBLISHDATE, VIDEO_RAW FROM VIDEO";
    NSArray *result = [self loadDataFromDB:query];
    return result;
}

- (bool)saveVideoData:(NSString *)videoId
        videoFilename:(NSString *)videoFilename
            videoItag:(NSString *)videoItag
           videoTitle:(NSString *)videoTitle
     videoDescription:(NSString *)videoDescription
        videoDuration:(NSString *)videoDuration
       videoChannelId:(NSString *)videoChannelId
    videoChannelTitle:(NSString *)videoChannelTitle
     videoPublishedAt:(NSString *)videoPublishedAt
                  raw:(NSString *)raw
{
    videoTitle = [videoTitle stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    videoDescription = [videoDescription stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    videoChannelTitle = [videoChannelTitle stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    NSString *rawString = [NSString stringWithFormat:@"%@",raw];
    rawString = [rawString stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    
    NSString *queryCheck = [NSString stringWithFormat:@"SELECT VIDEO_ID FROM VIDEO WHERE VIDEO_ID = '%@' AND VIDEO_ITAG = '%@'",videoId,videoItag];
    NSArray *queryCheckResult = [self loadDataFromDB:queryCheck];
    if([queryCheckResult count] != 0) {
        NSString *queryUpdate = [NSString stringWithFormat:@"UPDATE VIDEO SET VIDEO_FILENAME = '%@', VIDEO_ITAG = '%@', VIDEO_TITLE = '%@', VIDEO_DESCRIPTION = '%@', VIDEO_DURATION = '%@', VIDEO_CHANNELID = '%@', VIDEO_CHANNELTITLE = '%@', VIDEO_PUBLISHDATE = '%@', VIDEO_RAW = '%@' WHERE VIDEO_ID = '%@'",videoFilename,videoItag,videoTitle,videoDescription,videoDuration,videoChannelId,videoChannelTitle,videoPublishedAt,rawString,videoId];
        [self executeQuery:queryUpdate];
    } else {
        NSString *queryInsert = [NSString stringWithFormat:@"INSERT INTO VIDEO (VIDEO_ID, VIDEO_FILENAME, VIDEO_ITAG, VIDEO_TITLE, VIDEO_DESCRIPTION, VIDEO_DURATION, VIDEO_CHANNELID, VIDEO_CHANNELTITLE, VIDEO_PUBLISHDATE, VIDEO_RAW) VALUES ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",videoId,videoFilename,videoItag,videoTitle,videoDescription,videoDuration,videoChannelId,videoChannelTitle,videoPublishedAt,rawString];
        [self executeQuery:queryInsert];
    }
    return true;
}

- (bool)saveVideoDataThumb:(NSString *)videoId
             videoFilename:(NSString *)videoThumb
{
    NSString *queryCheck = [NSString stringWithFormat:@"SELECT VIDEO_ID FROM VIDEO WHERE VIDEO_ID = '%@'",videoId];
    NSArray *queryCheckResult = [self loadDataFromDB:queryCheck];
    if([queryCheckResult count] != 0) {
        NSString *queryUpdate = [NSString stringWithFormat:@"UPDATE VIDEO SET VIDEO_THUMB = '%@' WHERE VIDEO_ID = '%@'",videoThumb,videoId];
        [self executeQuery:queryUpdate];
    } else {
        NSString *queryInsert = [NSString stringWithFormat:@"INSERT INTO VIDEO (VIDEO_ID, VIDEO_THUMB) VALUES ('%@','%@')",videoId,videoThumb];
        [self executeQuery:queryInsert];
    }
    return true;
}

- (bool)deleteVideoData:(NSString *)recordId {
    NSString *queryDelete = [NSString stringWithFormat:@"DELETE FROM VIDEO 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
