package com.ustadmobile.core.db.dao

import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
import com.ustadmobile.door.flow.doorFlow
import com.ustadmobile.door.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapNextRow
import com.ustadmobile.door.jdbc.ext.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.EditAndViewPermission
import com.ustadmobile.lib.db.composites.PermissionPair
import com.ustadmobile.lib.db.entities.SystemPermission
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class SystemPermissionDao_JdbcImpl(
  public val _db: RoomDatabase,
) : SystemPermissionDao() {
  public val _insertAdapterSystemPermission_upsert: EntityInsertionAdapter<SystemPermission> =
      object : EntityInsertionAdapter<SystemPermission>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT OR REPLACE INTO SystemPermission (spUid, spToPersonUid, spToGroupUid, spPermissionsFlag, spLastModified, spIsDeleted) VALUES(?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO SystemPermission (spUid, spToPersonUid, spToGroupUid, spPermissionsFlag, spLastModified, spIsDeleted) VALUES(COALESCE(?,nextval('SystemPermission_spUid_seq')), ?, ?, ?, ?, ?) ON CONFLICT (spUid) DO UPDATE SET spToPersonUid = excluded.spToPersonUid,spToGroupUid = excluded.spToGroupUid,spPermissionsFlag = excluded.spPermissionsFlag,spLastModified = excluded.spLastModified,spIsDeleted = excluded.spIsDeleted" + if(returnsId) { " RETURNING spUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: SystemPermission) {
      if(entity.spUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.spUid)
      }
      stmt.setLong(2, entity.spToPersonUid)
      stmt.setLong(3, entity.spToGroupUid)
      stmt.setLong(4, entity.spPermissionsFlag)
      stmt.setLong(5, entity.spLastModified)
      stmt.setBoolean(6, entity.spIsDeleted)
    }
  }

  override suspend fun upsertAsync(systemPermissions: SystemPermission) {
    _insertAdapterSystemPermission_upsert.insertAsync(systemPermissions)
  }

  override suspend fun findAllByPersonUid(accountPersonUid: Long, includeDeleted: Boolean):
      List<SystemPermission> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SystemPermission.*
    |          FROM SystemPermission
    |         WHERE SystemPermission.spToPersonUid = ?
    |           AND (CAST(? AS INTEGER) = 1 OR NOT SystemPermission.spIsDeleted)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setBoolean(2,includeDeleted)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_spUid = _result.getLong("spUid")
        val _tmp_spToPersonUid = _result.getLong("spToPersonUid")
        val _tmp_spToGroupUid = _result.getLong("spToGroupUid")
        val _tmp_spPermissionsFlag = _result.getLong("spPermissionsFlag")
        val _tmp_spLastModified = _result.getLong("spLastModified")
        val _tmp_spIsDeleted = _result.getBoolean("spIsDeleted")
        SystemPermission().apply {
          this.spUid = _tmp_spUid
          this.spToPersonUid = _tmp_spToPersonUid
          this.spToGroupUid = _tmp_spToGroupUid
          this.spPermissionsFlag = _tmp_spPermissionsFlag
          this.spLastModified = _tmp_spLastModified
          this.spIsDeleted = _tmp_spIsDeleted
        }
      }
    }
  }

  override suspend fun findAllByPersonUidEntities(accountPersonUid: Long): List<SystemPermission> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SystemPermission.*
    |          FROM SystemPermission
    |         WHERE SystemPermission.spToPersonUid = ?
    |         
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_spUid = _result.getLong("spUid")
        val _tmp_spToPersonUid = _result.getLong("spToPersonUid")
        val _tmp_spToGroupUid = _result.getLong("spToGroupUid")
        val _tmp_spPermissionsFlag = _result.getLong("spPermissionsFlag")
        val _tmp_spLastModified = _result.getLong("spLastModified")
        val _tmp_spIsDeleted = _result.getBoolean("spIsDeleted")
        SystemPermission().apply {
          this.spUid = _tmp_spUid
          this.spToPersonUid = _tmp_spToPersonUid
          this.spToGroupUid = _tmp_spToGroupUid
          this.spPermissionsFlag = _tmp_spPermissionsFlag
          this.spLastModified = _tmp_spLastModified
          this.spIsDeleted = _tmp_spIsDeleted
        }
      }
    }
  }

  override fun findByPersonUidAsFlow(accountPersonUid: Long): Flow<SystemPermission?> =
      _db.doorFlow(arrayOf("SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT SystemPermission.*
      |          FROM SystemPermission
      |         WHERE SystemPermission.spToPersonUid = ?
      |         LIMIT 1
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_spUid = _result.getLong("spUid")
          val _tmp_spToPersonUid = _result.getLong("spToPersonUid")
          val _tmp_spToGroupUid = _result.getLong("spToGroupUid")
          val _tmp_spPermissionsFlag = _result.getLong("spPermissionsFlag")
          val _tmp_spLastModified = _result.getLong("spLastModified")
          val _tmp_spIsDeleted = _result.getBoolean("spIsDeleted")
          SystemPermission().apply {
            this.spUid = _tmp_spUid
            this.spToPersonUid = _tmp_spToPersonUid
            this.spToGroupUid = _tmp_spToGroupUid
            this.spPermissionsFlag = _tmp_spPermissionsFlag
            this.spLastModified = _tmp_spLastModified
            this.spIsDeleted = _tmp_spIsDeleted
          }
        }
      }
    }
  }

  override suspend fun findByPersonUid(accountPersonUid: Long): SystemPermission =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT SystemPermission.*
    |          FROM SystemPermission
    |         WHERE SystemPermission.spToPersonUid = ?
    |         LIMIT 1
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow() {
        val _tmp_spUid = _result.getLong("spUid")
        val _tmp_spToPersonUid = _result.getLong("spToPersonUid")
        val _tmp_spToGroupUid = _result.getLong("spToGroupUid")
        val _tmp_spPermissionsFlag = _result.getLong("spPermissionsFlag")
        val _tmp_spLastModified = _result.getLong("spLastModified")
        val _tmp_spIsDeleted = _result.getBoolean("spIsDeleted")
        SystemPermission().apply {
          this.spUid = _tmp_spUid
          this.spToPersonUid = _tmp_spToPersonUid
          this.spToGroupUid = _tmp_spToGroupUid
          this.spPermissionsFlag = _tmp_spPermissionsFlag
          this.spLastModified = _tmp_spLastModified
          this.spIsDeleted = _tmp_spIsDeleted
        }
      }
    }
  }

  override suspend fun personHasSystemPermission(accountPersonUid: Long, permission: Long): Boolean
      = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT 
    |        EXISTS(SELECT 1
    |                 FROM SystemPermission
    |                WHERE ? != 0 
    |                  AND SystemPermission.spToPersonUid = ?
    |                  AND (SystemPermission.spPermissionsFlag &
    |    
    |          ?
    |        
    |        ) > 0
    |                  AND NOT SystemPermission.spIsDeleted)
    |      
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,permission)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(false) {
        _result.getBoolean(1)
      }
    }
  }

  override fun personHasSystemPermissionAsFlow(accountPersonUid: Long, permission: Long):
      Flow<Boolean> = _db.doorFlow(arrayOf("SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT 
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |    
      |          ?
      |        
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |      
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,permission)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(false) {
          _result.getBoolean(1)
        }
      }
    }
  }

  override fun personHasSystemPermissionPairAsFlow(
    accountPersonUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): Flow<PermissionPair> = _db.doorFlow(arrayOf("SystemPermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT (
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |    
      |                ?
      |                
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    ) as firstPermission,
      |                (
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |    
      |                ?
      |                
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    ) as secondPermission
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,firstPermission)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,secondPermission)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow() {
          val _tmp_firstPermission = _result.getBoolean("firstPermission")
          val _tmp_secondPermission = _result.getBoolean("secondPermission")
          PermissionPair().apply {
            this.firstPermission = _tmp_firstPermission
            this.secondPermission = _tmp_secondPermission
          }
        }
      }
    }
  }

  override suspend fun personHasSystemPermissionPair(
    accountPersonUid: Long,
    firstPermission: Long,
    secondPermission: Long,
  ): PermissionPair = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT (
    |        EXISTS(SELECT 1
    |                 FROM SystemPermission
    |                WHERE ? != 0 
    |                  AND SystemPermission.spToPersonUid = ?
    |                  AND (SystemPermission.spPermissionsFlag &
    |    
    |                ?
    |                
    |        ) > 0
    |                  AND NOT SystemPermission.spIsDeleted)
    |    ) as firstPermission,
    |                (
    |        EXISTS(SELECT 1
    |                 FROM SystemPermission
    |                WHERE ? != 0 
    |                  AND SystemPermission.spToPersonUid = ?
    |                  AND (SystemPermission.spPermissionsFlag &
    |    
    |                ?
    |                
    |        ) > 0
    |                  AND NOT SystemPermission.spIsDeleted)
    |    ) as secondPermission
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,firstPermission)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,secondPermission)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow() {
        val _tmp_firstPermission = _result.getBoolean("firstPermission")
        val _tmp_secondPermission = _result.getBoolean("secondPermission")
        PermissionPair().apply {
          this.firstPermission = _tmp_firstPermission
          this.secondPermission = _tmp_secondPermission
        }
      }
    }
  }

  override fun personHasEditAndViewPermissionForPersonAsFlow(accountPersonUid: Long,
      otherPersonUid: Long): Flow<EditAndViewPermission> = _db.doorFlow(arrayOf("PersonParentJoin",
      "SystemPermission", "ClazzEnrolment", "CoursePermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        /* Similar to the query used on list all persons, however it uses the the otherpersonuid param 
      |         * in the WHERE clauses to narrow down the search. 
      |         */
      |        WITH CanViewPersonUidsViaCoursePermission(personUid) AS
      |              /* Select personUids that can be viewed based on CoursePermission given to the active
      |               * user for their enrolment role in that course
      |              */
      |              (SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid
      |                 FROM ClazzEnrolment ClazzEnrolment_ForActiveUser
      |                      JOIN CoursePermission 
      |                           ON CoursePermission.cpClazzUid = ClazzEnrolment_ForActiveUser.clazzEnrolmentClazzUid
      |                          AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForActiveUser.clazzEnrolmentRole
      |                          AND (CoursePermission.cpPermissionsFlag & 8192) > 0
      |                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember
      |                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                              AND ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid = ?
      |                WHERE ? != 0
      |                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentPersonUid = ?
      |                  AND ClazzEnrolment_ForActiveUser.clazzEnrolmentActive
      |              
      |               UNION
      |               /* Select personUids that can be viewed based on CoursePermission for the active user
      |                  where the CoursePermission is granted directly to them
      |                */   
      |               SELECT DISTINCT ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid AS personUid
      |                 FROM CoursePermission
      |                      JOIN ClazzEnrolment ClazzEnrolment_ForClazzMember
      |                           ON ClazzEnrolment_ForClazzMember.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                              AND ClazzEnrolment_ForClazzMember.clazzEnrolmentPersonUid = ?
      |                WHERE ? != 0
      |                  AND CoursePermission.cpToPersonUid = ?)
      |                  
      |        SELECT (    (SELECT ? = ?)
      |                 OR EXISTS(SELECT 1
      |                             FROM PersonParentJoin
      |                            WHERE PersonParentJoin.ppjMinorPersonUid = ?
      |                              AND PersonParentJoin.ppjParentPersonUid = ?)
      |                 OR (SELECT 
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |     
      |                            8192
      |                            
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    )
      |                 OR (SELECT ? IN 
      |                             (SELECT CanViewPersonUidsViaCoursePermission.personUid
      |                                 FROM CanViewPersonUidsViaCoursePermission))           
      |               ) AS hasViewPermission,
      |               
      |               (   (SELECT ? = ?)
      |                OR EXISTS(SELECT 1
      |                             FROM PersonParentJoin
      |                            WHERE PersonParentJoin.ppjMinorPersonUid = ?
      |                              AND PersonParentJoin.ppjParentPersonUid = ?)
      |                OR (SELECT 
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |     
      |                            32768
      |                            
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    )
      |                ) AS hasEditPermission
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,otherPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,accountPersonUid)
      _stmt.setLong(4,otherPersonUid)
      _stmt.setLong(5,accountPersonUid)
      _stmt.setLong(6,accountPersonUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,otherPersonUid)
      _stmt.setLong(9,otherPersonUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,accountPersonUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,otherPersonUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setLong(15,otherPersonUid)
      _stmt.setLong(16,otherPersonUid)
      _stmt.setLong(17,accountPersonUid)
      _stmt.setLong(18,accountPersonUid)
      _stmt.setLong(19,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow() {
          val _tmp_hasViewPermission = _result.getBoolean("hasViewPermission")
          val _tmp_hasEditPermission = _result.getBoolean("hasEditPermission")
          EditAndViewPermission().apply {
            this.hasViewPermission = _tmp_hasViewPermission
            this.hasEditPermission = _tmp_hasEditPermission
          }
        }
      }
    }
  }
}
