package com.ustadmobile.libcache.db

import com.ustadmobile.door.DoorDatabaseJdbc
import com.ustadmobile.door.DoorPrimaryKeyManager
import com.ustadmobile.door.ext.doorDatabaseMetadata
import com.ustadmobile.door.ext.execSqlBatch
import com.ustadmobile.door.ext.mutableLinkedListOf
import com.ustadmobile.door.ext.nodeIdAuthCache
import com.ustadmobile.door.ext.rootDatabase
import com.ustadmobile.door.jdbc.DataSource
import com.ustadmobile.door.log.DoorLogger
import com.ustadmobile.door.room.InvalidationTracker
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.door.room.RoomDatabaseJdbcImplHelper
import com.ustadmobile.door.room.RoomJdbcImpl
import com.ustadmobile.door.util.NodeIdAuthCache
import com.ustadmobile.door.util.systemTimeInMillis
import com.ustadmobile.libcache.db.dao.CacheEntryDao
import com.ustadmobile.libcache.db.dao.CacheEntryDao_JdbcImpl
import com.ustadmobile.libcache.db.dao.RequestedEntryDao
import com.ustadmobile.libcache.db.dao.RequestedEntryDao_JdbcImpl
import com.ustadmobile.libcache.db.dao.RetentionLockDao
import com.ustadmobile.libcache.db.dao.RetentionLockDao_JdbcImpl
import kotlin.Int
import kotlin.String
import kotlin.collections.List
import kotlin.collections.toTypedArray

public class UstadCacheDb_JdbcImpl(
  override val doorJdbcSourceDatabase: RoomDatabase?,
  override val dataSource: DataSource,
  dbUrl: String,
  override val dbName: String,
  override val jdbcQueryTimeout: Int,
  jdbcDbType: Int,
  logger: DoorLogger,
) : UstadCacheDb(), DoorDatabaseJdbc, RoomJdbcImpl {
  override val dbVersion: Int
    get() = 9

  override val jdbcImplHelper: RoomDatabaseJdbcImplHelper = RoomDatabaseJdbcImplHelper(
        dataSource = dataSource,
        db = this,
        dbUrl = dbUrl,
        dbName = dbName,
        logger = logger,
        tableNames = this::class.doorDatabaseMetadata().allTables,
        invalidationTracker =
            InvalidationTracker(*this::class.doorDatabaseMetadata().allTables.toTypedArray()),
        dbType = jdbcDbType,
      )


  override val realNodeIdAuthCache: NodeIdAuthCache by lazy {
        if(this == rootDatabase) {
          val nodeIdAuthCache = NodeIdAuthCache(this)
          nodeIdAuthCache
        } else {
          rootDatabase.nodeIdAuthCache
        }
      }


  override val realPrimaryKeyManager: DoorPrimaryKeyManager by lazy {
        DoorPrimaryKeyManager(UstadCacheDb::class.doorDatabaseMetadata().replicateEntities.keys)
      }


  override val invalidationTracker: InvalidationTracker
    get() = jdbcImplHelper.invalidationTracker

  public val _CacheEntryDao: CacheEntryDao_JdbcImpl by lazy { CacheEntryDao_JdbcImpl(this) }

  override val cacheEntryDao: CacheEntryDao
    get() = _CacheEntryDao

  public val _RequestedEntryDao: RequestedEntryDao_JdbcImpl by
      lazy { RequestedEntryDao_JdbcImpl(this) }

  override val requestedEntryDao: RequestedEntryDao
    get() = _RequestedEntryDao

  public val _RetentionLockDao: RetentionLockDao_JdbcImpl by
      lazy { RetentionLockDao_JdbcImpl(this) }

  override val retentionLockDao: RetentionLockDao
    get() = _RetentionLockDao

  override fun createAllTables(): List<String> {
    val _stmtList = mutableLinkedListOf<String>()
    when(jdbcImplHelper.dbType) {
      1 ->  {
        // - create for this SQLite 
        _stmtList +=
            "CREATE TABLE IF NOT EXISTS _doorwayinfo (dbVersion int primary key, dbHash varchar(255))"
         _stmtList += "INSERT INTO _doorwayinfo VALUES (9, '')"
        //Begin: Create table CacheEntry for SQLite
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE CacheEntry RENAME to CacheEntry_OLD")
        END MIGRATION */
        _stmtList +=
            "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 , uncompressedSize  INTEGER  NOT NULL  DEFAULT 0 )"
        _stmtList += "CREATE INDEX idx_lastAccessed ON CacheEntry (lastAccessed)"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) SELECT key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize FROM CacheEntry_OLD")
        _stmt.executeUpdate("DROP TABLE CacheEntry_OLD")
        END MIGRATION*/
        //End: Create table CacheEntry for SQLite

        //Begin: Create table RequestedEntry for SQLite
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE RequestedEntry RENAME to RequestedEntry_OLD")
        END MIGRATION */
        _stmtList +=
            "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 )"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO RequestedEntry (requestSha256, requestedKey, batchId, id) SELECT requestSha256, requestedKey, batchId, id FROM RequestedEntry_OLD")
        _stmt.executeUpdate("DROP TABLE RequestedEntry_OLD")
        END MIGRATION*/
        //End: Create table RequestedEntry for SQLite

        //Begin: Create table RetentionLock for SQLite
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE RetentionLock RENAME to RetentionLock_OLD")
        END MIGRATION */
        _stmtList +=
            "CREATE TABLE IF NOT EXISTS RetentionLock (  lockKey  TEXT  NOT NULL , lockRemark  TEXT  NOT NULL , lockId  INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL )"
        _stmtList += "CREATE INDEX idx_lockKey ON RetentionLock (lockKey)"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO RetentionLock (lockKey, lockRemark, lockId) SELECT lockKey, lockRemark, lockId FROM RetentionLock_OLD")
        _stmt.executeUpdate("DROP TABLE RetentionLock_OLD")
        END MIGRATION*/
        //End: Create table RetentionLock for SQLite

      }
      2 ->  {
        // - create for this PostgreSQL 
        _stmtList +=
            "CREATE TABLE IF NOT EXISTS _doorwayinfo (dbVersion int primary key, dbHash varchar(255))"
         _stmtList += "INSERT INTO _doorwayinfo VALUES (9, '')"
        //Begin: Create table CacheEntry for PostgreSQL
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE CacheEntry RENAME to CacheEntry_OLD")
        END MIGRATION */
        _stmtList +=
            "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  BIGINT  NOT NULL , lastValidated  BIGINT  NOT NULL , integrity  TEXT , responseHeaders  TEXT  NOT NULL , storageUri  TEXT  NOT NULL , storageSize  BIGINT  NOT NULL , uncompressedSize  BIGINT  NOT NULL  DEFAULT 0 )"
        _stmtList += "CREATE INDEX idx_lastAccessed ON CacheEntry (lastAccessed)"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO CacheEntry (key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize) SELECT key, url, message, statusCode, cacheFlags, method, lastAccessed, lastValidated, integrity, responseHeaders, storageUri, storageSize, uncompressedSize FROM CacheEntry_OLD")
        _stmt.executeUpdate("DROP TABLE CacheEntry_OLD")
        END MIGRATION*/
        //End: Create table CacheEntry for PostgreSQL

        //Begin: Create table RequestedEntry for PostgreSQL
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE RequestedEntry RENAME to RequestedEntry_OLD")
        END MIGRATION */
        _stmtList +=
            "CREATE TABLE IF NOT EXISTS RequestedEntry (  requestSha256  TEXT  NOT NULL , requestedKey  TEXT  NOT NULL , batchId  INTEGER  NOT NULL , id  SERIAL  PRIMARY KEY  NOT NULL )"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO RequestedEntry (requestSha256, requestedKey, batchId, id) SELECT requestSha256, requestedKey, batchId, id FROM RequestedEntry_OLD")
        _stmt.executeUpdate("DROP TABLE RequestedEntry_OLD")
        END MIGRATION*/
        //End: Create table RequestedEntry for PostgreSQL

        //Begin: Create table RetentionLock for PostgreSQL
        /* START MIGRATION: 
        _stmt.executeUpdate("ALTER TABLE RetentionLock RENAME to RetentionLock_OLD")
        END MIGRATION */
        _stmtList +=
            "CREATE TABLE IF NOT EXISTS RetentionLock (  lockKey  TEXT  NOT NULL , lockRemark  TEXT  NOT NULL , lockId  BIGSERIAL  PRIMARY KEY  NOT NULL )"
        _stmtList += "CREATE INDEX idx_lockKey ON RetentionLock (lockKey)"
        /* START MIGRATION: 
        _stmt.executeUpdate("INSERT INTO RetentionLock (lockKey, lockRemark, lockId) SELECT lockKey, lockRemark, lockId FROM RetentionLock_OLD")
        _stmt.executeUpdate("DROP TABLE RetentionLock_OLD")
        END MIGRATION*/
        //End: Create table RetentionLock for PostgreSQL

      }
    }
    return _stmtList
  }

  public fun makeClearAllTablesSql(): List<String> {
    val _stmtList = mutableListOf<String>()
    _stmtList += "DELETE FROM CacheEntry"
    _stmtList += "DELETE FROM RequestedEntry"
    _stmtList += "DELETE FROM RetentionLock"
    return _stmtList
  }

  override fun clearAllTables() {
    execSqlBatch(*makeClearAllTablesSql().toTypedArray())
  }

  override fun close() {
    jdbcImplHelper.close()
  }
}
