package com.ustadmobile.libcache.db

import com.ustadmobile.door.DatabaseBuilder
import com.ustadmobile.door.migration.DoorMigrationStatementList
import com.ustadmobile.door.util.systemTimeInMillis

val MIGRATE_1_2 = DoorMigrationStatementList(1, 2) { db ->
    val lastValidatedTime = systemTimeInMillis()
    buildList {
        add("ALTER TABLE CacheEntry RENAME to CacheEntry_OLD")
        add("CREATE TABLE IF NOT EXISTS CacheEntry (  url  TEXT  NOT NULL , message  TEXT  NOT NULL , statusCode  INTEGER  NOT NULL , cacheFlags  INTEGER  NOT NULL , method  INTEGER  NOT NULL , lastAccessed  INTEGER  NOT NULL , lastValidated  INTEGER  NOT NULL , responseBodySha256  TEXT , responseHeaders  TEXT  NOT NULL , ceId  INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL )")
        add("""
            INSERT INTO CacheEntry (url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, responseBodySha256, responseHeaders, ceId) 
            SELECT url, 'OK', 200, cacheFlags, method, lastAccessed, $lastValidatedTime, responseBodySha256, responseHeaders, ceId 
            FROM CacheEntry_OLD
            """
        )
        add("DROP TABLE CacheEntry_OLD")
    }
}

/**
 * Converts from using the url as the lookup to using a key (MD5 of the URL, base64 encoded)
 */
val MIGRATE_2_3 = DoorMigrationStatementList(2,3) {
    buildList {
        add("DROP TABLE CacheEntry")
        add("DROP TABLE RequestedEntry")
        add("CREATE TABLE IF NOT EXISTS CacheEntry (  key  TEXT  PRIMARY KEY  NOT NULL , url  TEXT  NOT NULL , message  TEXT  NOT NULL , statusCode  INTEGER  NOT NULL , cacheFlags  INTEGER  NOT NULL , method  INTEGER  NOT NULL , lastAccessed  INTEGER  NOT NULL , lastValidated  INTEGER  NOT NULL , responseBodySha256  TEXT , responseHeaders  TEXT  NOT NULL )")
        add("CREATE TABLE IF NOT EXISTS RequestedEntry (  requestSha256  TEXT  NOT NULL , requestedKey  TEXT  NOT NULL , batchId  INTEGER  NOT NULL , id  INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL )")
    }
}

/**
 * Add RetentionLock table
 * Update body table to add size field
 */
val MIGRATE_3_4 = DoorMigrationStatementList(3, 4) {
    buildList {
        add("CREATE TABLE IF NOT EXISTS RetentionLock (  lockKey  TEXT  NOT NULL , lockRemark  TEXT  NOT NULL , lockId  INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL )")
        add("ALTER TABLE RequestBody ADD COLUMN bodySize  INTEGER  NOT NULL  DEFAULT 0")
    }
}


/**
 * Drop the one-many cache entry to request body concept. It isn't needed because where duplicate
 * urls are likely the urls themselves are a function of the sha-256 sum.
 *
 * CacheEntry now has the storageUri and size on it directly. This is still not deployed on production,
 * so migration is mostly destructive.
 */
val MIGRATE_4_5 = DoorMigrationStatementList(4, 5) {
    buildList {
        add("DROP TABLE IF EXISTS RequestBody")
        add("DROP TABLE IF EXISTS CacheEntry")
        add("CREATE TABLE IF NOT EXISTS CacheEntry (  key  TEXT  PRIMARY KEY  NOT NULL , url  TEXT  NOT NULL , message  TEXT  NOT NULL , statusCode  INTEGER  NOT NULL , cacheFlags  INTEGER  NOT NULL , method  INTEGER  NOT NULL , lastAccessed  INTEGER  NOT NULL , lastValidated  INTEGER  NOT NULL , responseBodySha256  TEXT , responseHeaders  TEXT  NOT NULL , storageUri  TEXT  NOT NULL , storageSize  INTEGER  NOT NULL )")
    }
}

/**
 * Add index for last accessed column (sort criteria) and lock key
 */
val MIGRATE_5_6 = DoorMigrationStatementList(5, 6) {
    buildList {
        add("CREATE INDEX idx_lastAccessed ON CacheEntry (lastAccessed)")
        add("CREATE INDEX idx_lockKey ON RetentionLock (lockKey)")
    }
}

/**
 * Rename responsebody field to integrity field on CacheEntry
 */
val MIGRATE_6_7 = DoorMigrationStatementList(6, 7) {
    buildList {
        add("DROP TABLE IF EXISTS CacheEntry")
        add("CREATE TABLE IF NOT EXISTS CacheEntry (  key  TEXT  PRIMARY KEY  NOT NULL , url  TEXT  NOT NULL , message  TEXT  NOT NULL , statusCode  INTEGER  NOT NULL , cacheFlags  INTEGER  NOT NULL , method  INTEGER  NOT NULL , lastAccessed  INTEGER  NOT NULL , lastValidated  INTEGER  NOT NULL , integrity  TEXT , responseHeaders  TEXT  NOT NULL , storageUri  TEXT  NOT NULL , storageSize  INTEGER  NOT NULL )")
        add("CREATE INDEX idx_lastAccessed ON CacheEntry (lastAccessed)")
    }
}

/**
 * Add uncompressedSize field
 */
val MIGRATE_7_8 = DoorMigrationStatementList(7, 8) {
    buildList {
        add("ALTER TABLE CacheEntry ADD COLUMN uncompressedSize INTEGER NOT NULL DEFAULT 0")
        add("UPDATE CacheEntry SET uncompressedSize = storageSize")
    }
}

val MIGRATE_9_10 = DoorMigrationStatementList(9, 10) {
    listOf("CREATE TABLE IF NOT EXISTS NeighborCache (  neighborUid  INTEGER  PRIMARY KEY  NOT NULL , neighborIp  TEXT  NOT NULL , neighborUdpPort  INTEGER  NOT NULL , neighborHttpPort  INTEGER  NOT NULL , neighborDiscovered  INTEGER  NOT NULL , neighborPingTime  INTEGER  NOT NULL )")
}

val MIGRATE_10_11 = DoorMigrationStatementList(10, 11) {
    listOf("CREATE TABLE IF NOT EXISTS NeighborCacheEntry (  nceNeighborUid  INTEGER  NOT NULL , nceUrlHash  INTEGER  NOT NULL , PRIMARY KEY (nceNeighborUid, nceUrlHash) )")
}

//On client devices - add the new cache entry trigger for distributed caching
val MIGRATE_11_12_CLIENT = DoorMigrationStatementList(11, 12) {
    listOf(
        "CREATE TABLE IF NOT EXISTS NewCacheEntry (  cacheEntryKey  TEXT  PRIMARY KEY  NOT NULL , nceUrl  TEXT  NOT NULL )",
        """
        CREATE TRIGGER NewCacheEntryTrigger 
            AFTER INSERT ON CacheEntry
            BEGIN
               INSERT OR REPLACE INTO NewCacheEntry(cacheEntryKey, nceUrl) VALUES(NEW.key, NEW.url);
            END
    """)
}

val MIGRATE_11_12_SERVER = DoorMigrationStatementList(11, 12) {
    emptyList()
}

val MIGRATE_12_13 = DoorMigrationStatementList(12, 13) {
    listOf(
        "DROP TABLE IF EXISTS NeighborCache",
        "CREATE TABLE IF NOT EXISTS NeighborCache (  neighborUid  INTEGER  PRIMARY KEY  NOT NULL , neighborIp  TEXT  NOT NULL , neighborUdpPort  INTEGER  NOT NULL , neighborHttpPort  INTEGER  NOT NULL , neighborDiscovered  INTEGER  NOT NULL , neighborPingTime  INTEGER  NOT NULL , neighborLastSeen  INTEGER  NOT NULL , neighborStatus  INTEGER  NOT NULL )"
    )
}

val MIGRATE_13_14 = DoorMigrationStatementList(13, 14) {
    listOf(
        "ALTER TABLE NeighborCache ADD COLUMN neighborDeviceName TEXT NOT NULL DEFAULT ''"
    )
}

//Previous version of migration 11-12 missed creating NewCacheEntry
val MIGRATE_14_15 = DoorMigrationStatementList(14, 15) {
    listOf(
        "CREATE TABLE IF NOT EXISTS NewCacheEntry (  cacheEntryKey  TEXT  PRIMARY KEY  NOT NULL , nceUrl  TEXT  NOT NULL )",
    )
}


fun DatabaseBuilder<UstadCacheDb>.addCacheDbMigrations(): DatabaseBuilder<UstadCacheDb> {
    return addMigrations(
        MIGRATE_1_2, MIGRATE_2_3, MIGRATE_3_4, MIGRATE_4_5,
        MIGRATE_5_6, MIGRATE_6_7, MIGRATE_7_8, MIGRATE_9_10, MIGRATE_10_11, MIGRATE_12_13,
        MIGRATE_13_14, MIGRATE_14_15,
    )
}