package com.taf.attendance.data

import android.content.Context
import androidx.room.*
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.taf.attendance.model.AttendanceRecord
import com.taf.attendance.model.UserFace
import com.taf.attendance.model.Branch

@Dao
interface AttendanceDao {
    @Query("SELECT * FROM records ORDER BY time DESC")
    suspend fun getAll(): List<AttendanceRecord>

    @Query("SELECT * FROM records WHERE time BETWEEN :start AND :end ORDER BY time ASC")
    suspend fun getRange(start: Long, end: Long): List<AttendanceRecord>

    @Query("SELECT * FROM records WHERE time >= :since ORDER BY time DESC LIMIT 1")
    suspend fun getLastSince(since: Long): AttendanceRecord?

    @Query("SELECT * FROM records WHERE synced = 0")
    suspend fun getUnsynced(): List<AttendanceRecord>

    @Query("SELECT COUNT(*) FROM records WHERE synced = 0")
    suspend fun getUnsyncedCount(): Int

    @Insert
    suspend fun insert(record: AttendanceRecord)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(records: List<AttendanceRecord>)

    @Query("DELETE FROM records WHERE id = :id")
    suspend fun delete(id: Int)

    @Query("UPDATE records SET synced = 1 WHERE id = :id")
    suspend fun markSynced(id: Int)

    @Query("DELETE FROM records")
    suspend fun clear()
}

@Dao
interface LeaveDao {
    @Query("SELECT * FROM leave_requests ORDER BY createdAt DESC")
    fun getAllLeaveRequests(): kotlinx.coroutines.flow.Flow<List<LeaveRequestEntity>>

    @Query("SELECT * FROM leave_balances WHERE user_id = :userId")
    fun getLeaveBalances(userId: String): kotlinx.coroutines.flow.Flow<List<LeaveBalanceEntity>>

    @Insert(onConflict = androidx.room.OnConflictStrategy.REPLACE)
    suspend fun insertLeaveRequest(request: LeaveRequestEntity)

    @Insert(onConflict = androidx.room.OnConflictStrategy.REPLACE)
    suspend fun insertLeaveBalances(balances: List<LeaveBalanceEntity>)

    @Query("UPDATE leave_requests SET isSynced = 1 WHERE id = :id")
    suspend fun markSynced(id: Int)

    @Query("SELECT * FROM leave_requests WHERE isSynced = 0")
    suspend fun getUnsyncedRequests(): List<LeaveRequestEntity>
}

@Database(entities = [AttendanceRecord::class, UserFace::class, Branch::class, LeaveRequestEntity::class, LeaveBalanceEntity::class], version = 6)
abstract class AppDatabase : RoomDatabase() {
    abstract fun attendanceDao(): AttendanceDao
    abstract fun userFaceDao(): UserFaceDao
    abstract fun branchDao(): BranchDao
    abstract fun leaveDao(): LeaveDao

    companion object {
        @Volatile private var INSTANCE: AppDatabase? = null

        private val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    "CREATE TABLE IF NOT EXISTS user_faces (userId TEXT NOT NULL PRIMARY KEY, path TEXT NOT NULL, embedding TEXT NOT NULL)"
                )
            }
        }

        private val MIGRATION_3_4 = object : Migration(3, 4) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    "CREATE TABLE IF NOT EXISTS branches (branchId TEXT NOT NULL PRIMARY KEY, name TEXT NOT NULL, latitude REAL, longitude REAL)"
                )
            }
        }

        private val MIGRATION_4_5 = object : Migration(4, 5) {
            override fun migrate(db: SupportSQLiteDatabase) {
                // Add leave management tables (already handled by Room)
            }
        }

        private val MIGRATION_5_6 = object : Migration(5, 6) {
            override fun migrate(db: SupportSQLiteDatabase) {
                // Add synced column to user_faces table
                db.execSQL("ALTER TABLE user_faces ADD COLUMN synced INTEGER NOT NULL DEFAULT 0")
            }
        }

        fun get(context: Context): AppDatabase =
            INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "attendance.db"
                )
                    .addMigrations(MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5, MIGRATION_5_6)
                    .fallbackToDestructiveMigration()
                    .build()
                    .also { INSTANCE = it }
            }
    }
}