package com.placeholder.sherpai2.data.local

import androidx.room.Database
import androidx.room.RoomDatabase
import androidx.sqlite.db.SupportSQLiteDatabase
import androidx.room.migration.Migration
import com.placeholder.sherpai2.data.local.dao.*
import com.placeholder.sherpai2.data.local.entity.*

/**
 * AppDatabase - Complete database for SherpAI2
 *
 * VERSION 12 - Distribution-based rejection stats
 * - Added similarityStdDev, similarityMin to FaceModelEntity
 * - Enables self-calibrating threshold for face matching
 *
 * VERSION 10 - User Feedback Loop
 * - Added UserFeedbackEntity for storing user corrections
 * - Enables cluster refinement before training
 * - Ground truth data for improving clustering
 *
 * VERSION 9 - Enhanced Face Cache
 * - Added FaceCacheEntity for per-face metadata
 * - Stores quality scores, embeddings, bounding boxes
 * - Enables intelligent face filtering for clustering
 *
 * VERSION 8 - PHASE 2: Multi-centroid face models + age tagging
 * - Added PersonEntity.isChild, siblingIds, familyGroupId
 * - Changed FaceModelEntity.embedding → centroidsJson (multi-centroid)
 * - Added PersonAgeTagEntity table for searchable age tags
 *
 * MIGRATION STRATEGY:
 * - Development: fallbackToDestructiveMigration (fresh install)
 * - Production: Add migrations before release
 */
@Database(
    entities = [
        // ===== CORE ENTITIES =====
        ImageEntity::class,
        TagEntity::class,
        EventEntity::class,
        ImageTagEntity::class,
        ImageEventEntity::class,

        // ===== FACE RECOGNITION =====
        PersonEntity::class,
        FaceModelEntity::class,
        PhotoFaceTagEntity::class,
        PersonAgeTagEntity::class,
        FaceCacheEntity::class,
        UserFeedbackEntity::class,
        PersonStatisticsEntity::class,   // Pre-computed person stats

        // ===== COLLECTIONS =====
        CollectionEntity::class,
        CollectionImageEntity::class,
        CollectionFilterEntity::class
    ],
    version = 12,  // INCREMENTED for distribution-based rejection stats
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {

    // ===== CORE DAOs =====
    abstract fun imageDao(): ImageDao
    abstract fun tagDao(): TagDao
    abstract fun eventDao(): EventDao
    abstract fun imageTagDao(): ImageTagDao
    abstract fun imageEventDao(): ImageEventDao
    abstract fun imageAggregateDao(): ImageAggregateDao

    // ===== FACE RECOGNITION DAOs =====
    abstract fun personDao(): PersonDao
    abstract fun faceModelDao(): FaceModelDao
    abstract fun photoFaceTagDao(): PhotoFaceTagDao
    abstract fun personAgeTagDao(): PersonAgeTagDao
    abstract fun faceCacheDao(): FaceCacheDao
    abstract fun userFeedbackDao(): UserFeedbackDao
    abstract fun personStatisticsDao(): PersonStatisticsDao

    // ===== COLLECTIONS DAO =====
    abstract fun collectionDao(): CollectionDao
}

/**
 * MIGRATION 7 → 8 (Phase 2)
 *
 * Changes:
 * 1. Add isChild, siblingIds, familyGroupId to persons table
 * 2. Rename embedding → centroidsJson in face_models table
 * 3. Create person_age_tags table
 */
val MIGRATION_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase) {

        // ===== STEP 1: Update persons table =====
        database.execSQL("ALTER TABLE persons ADD COLUMN isChild INTEGER NOT NULL DEFAULT 0")
        database.execSQL("ALTER TABLE persons ADD COLUMN siblingIds TEXT DEFAULT NULL")
        database.execSQL("ALTER TABLE persons ADD COLUMN familyGroupId TEXT DEFAULT NULL")

        // Create index on familyGroupId for sibling queries
        database.execSQL("CREATE INDEX IF NOT EXISTS index_persons_familyGroupId ON persons(familyGroupId)")

        // ===== STEP 2: Update face_models table =====
        // Rename embedding column to centroidsJson
        // SQLite doesn't support RENAME COLUMN directly, so we need to:
        // 1. Create new table with new schema
        // 2. Copy data (converting single embedding to centroid JSON)
        // 3. Drop old table
        // 4. Rename new table

        // Create new table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS face_models_new (
                id TEXT PRIMARY KEY NOT NULL,
                personId TEXT NOT NULL,
                centroidsJson TEXT NOT NULL,
                trainingImageCount INTEGER NOT NULL,
                averageConfidence REAL NOT NULL,
                createdAt INTEGER NOT NULL,
                updatedAt INTEGER NOT NULL,
                lastUsed INTEGER,
                isActive INTEGER NOT NULL,
                FOREIGN KEY(personId) REFERENCES persons(id) ON DELETE CASCADE
            )
        """)

        // Copy data, converting embedding to centroidsJson format
        // This converts single embedding to a list with one centroid
        database.execSQL("""
            INSERT INTO face_models_new 
            SELECT 
                id,
                personId,
                '[{"embedding":' || REPLACE(REPLACE(embedding, ',', ','), ',', ',') || ',"effectiveTimestamp":' || createdAt || ',"ageAtCapture":null,"photoCount":' || trainingImageCount || ',"timeRangeMonths":12,"avgConfidence":' || averageConfidence || '}]' as centroidsJson,
                trainingImageCount,
                averageConfidence,
                createdAt,
                updatedAt,
                lastUsed,
                isActive
            FROM face_models
        """)

        // Drop old table
        database.execSQL("DROP TABLE face_models")

        // Rename new table
        database.execSQL("ALTER TABLE face_models_new RENAME TO face_models")

        // Recreate index
        database.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS index_face_models_personId ON face_models(personId)")

        // ===== STEP 3: Create person_age_tags table =====
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS person_age_tags (
                id TEXT PRIMARY KEY NOT NULL,
                personId TEXT NOT NULL,
                imageId TEXT NOT NULL,
                ageAtCapture INTEGER NOT NULL,
                tagValue TEXT NOT NULL,
                confidence REAL NOT NULL,
                createdAt INTEGER NOT NULL,
                FOREIGN KEY(personId) REFERENCES persons(id) ON DELETE CASCADE,
                FOREIGN KEY(imageId) REFERENCES images(imageId) ON DELETE CASCADE
            )
        """)

        // Create indices for fast lookups
        database.execSQL("CREATE INDEX IF NOT EXISTS index_person_age_tags_personId ON person_age_tags(personId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_person_age_tags_imageId ON person_age_tags(imageId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_person_age_tags_ageAtCapture ON person_age_tags(ageAtCapture)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_person_age_tags_tagValue ON person_age_tags(tagValue)")
    }
}

/**
 * MIGRATION 8 → 9 (Enhanced Face Cache)
 *
 * Changes:
 * 1. Create face_cache table for per-face metadata
 */
val MIGRATION_8_9 = object : Migration(8, 9) {
    override fun migrate(database: SupportSQLiteDatabase) {

        // Create face_cache table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS face_cache (
                imageId TEXT NOT NULL,
                faceIndex INTEGER NOT NULL,
                boundingBox TEXT NOT NULL,
                faceWidth INTEGER NOT NULL,
                faceHeight INTEGER NOT NULL,
                faceAreaRatio REAL NOT NULL,
                qualityScore REAL NOT NULL,
                isLargeEnough INTEGER NOT NULL,
                isFrontal INTEGER NOT NULL,
                hasGoodLighting INTEGER NOT NULL,
                embedding TEXT,
                confidence REAL NOT NULL,
                imageWidth INTEGER NOT NULL DEFAULT 0,
                imageHeight INTEGER NOT NULL DEFAULT 0,
                cacheVersion INTEGER NOT NULL DEFAULT 1,
                cachedAt INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY(imageId, faceIndex),
                FOREIGN KEY(imageId) REFERENCES images(imageId) ON DELETE CASCADE
            )
        """)

        // Create indices for fast queries
        database.execSQL("CREATE INDEX IF NOT EXISTS index_face_cache_imageId ON face_cache(imageId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_face_cache_qualityScore ON face_cache(qualityScore)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_face_cache_isLargeEnough ON face_cache(isLargeEnough)")
    }
}

/**
 * MIGRATION 9 → 10 (User Feedback Loop)
 *
 * Changes:
 * 1. Create user_feedback table for storing user corrections
 */
val MIGRATION_9_10 = object : Migration(9, 10) {
    override fun migrate(database: SupportSQLiteDatabase) {

        // Create user_feedback table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS user_feedback (
                id TEXT PRIMARY KEY NOT NULL,
                imageId TEXT NOT NULL,
                faceIndex INTEGER NOT NULL,
                clusterId INTEGER,
                personId TEXT,
                feedbackType TEXT NOT NULL,
                originalConfidence REAL NOT NULL,
                userNote TEXT,
                timestamp INTEGER NOT NULL,
                FOREIGN KEY(imageId) REFERENCES images(imageId) ON DELETE CASCADE,
                FOREIGN KEY(personId) REFERENCES persons(id) ON DELETE CASCADE
            )
        """)

        // Create indices for fast lookups
        database.execSQL("CREATE INDEX IF NOT EXISTS index_user_feedback_imageId ON user_feedback(imageId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_user_feedback_clusterId ON user_feedback(clusterId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_user_feedback_personId ON user_feedback(personId)")
        database.execSQL("CREATE INDEX IF NOT EXISTS index_user_feedback_feedbackType ON user_feedback(feedbackType)")
    }
}

/**
 * MIGRATION 10 → 11 (Person Statistics)
 *
 * Changes:
 * 1. Create person_statistics table for pre-computed aggregates
 */
val MIGRATION_10_11 = object : Migration(10, 11) {
    override fun migrate(database: SupportSQLiteDatabase) {

        // Create person_statistics table
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS person_statistics (
                personId TEXT PRIMARY KEY NOT NULL,
                photoCount INTEGER NOT NULL DEFAULT 0,
                firstPhotoDate INTEGER NOT NULL DEFAULT 0,
                lastPhotoDate INTEGER NOT NULL DEFAULT 0,
                averageConfidence REAL NOT NULL DEFAULT 0,
                agesWithPhotos TEXT,
                updatedAt INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY(personId) REFERENCES persons(id) ON DELETE CASCADE
            )
        """)

        // Index for sorting by photo count (People Dashboard)
        database.execSQL("CREATE INDEX IF NOT EXISTS index_person_statistics_photoCount ON person_statistics(photoCount)")
    }
}

/**
 * MIGRATION 11 → 12 (Distribution-based Rejection Stats)
 *
 * Changes:
 * 1. Add similarityStdDev column to face_models (default 0.05)
 * 2. Add similarityMin column to face_models (default 0.6)
 *
 * These fields enable self-calibrating thresholds during scanning.
 * During training, we compute stats from training sample similarities
 * and use (mean - 2*stdDev) as a floor for matching.
 */
val MIGRATION_11_12 = object : Migration(11, 12) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Add distribution stats columns with sensible defaults for existing models
        database.execSQL("ALTER TABLE face_models ADD COLUMN similarityStdDev REAL NOT NULL DEFAULT 0.05")
        database.execSQL("ALTER TABLE face_models ADD COLUMN similarityMin REAL NOT NULL DEFAULT 0.6")
    }
}

/**
 * PRODUCTION MIGRATION NOTES:
 *
 * Before shipping to users, update DatabaseModule to use migrations:
 *
 * Room.databaseBuilder(context, AppDatabase::class.java, "sherpai.db")
 *     .addMigrations(MIGRATION_7_8, MIGRATION_8_9, MIGRATION_9_10, MIGRATION_10_11, MIGRATION_11_12)  // Add all migrations
 *     // .fallbackToDestructiveMigration()  // Remove this
 *     .build()
 */