package com.fieldlabel.collector.data.local

import androidx.room.Database
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

@Database(
    entities = [
        CollectionRecordEntity::class,
        GeocodeCacheEntity::class,
        SpecimenPhotoEntity::class,
        CustomFieldDefEntity::class,
        CustomFieldValueEntity::class,
        RouteLogEntity::class,
        RoutePointEntity::class
    ],
    version = 5,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun collectionRecordDao(): CollectionRecordDao
    abstract fun geocodeCacheDao(): GeocodeCacheDao
    abstract fun specimenPhotoDao(): SpecimenPhotoDao
    abstract fun customFieldDefDao(): CustomFieldDefDao
    abstract fun customFieldValueDao(): CustomFieldValueDao
    abstract fun routeLogDao(): RouteLogDao
    abstract fun routePointDao(): RoutePointDao

    companion object {
        /** バージョン1→2: specimen_photos テーブルを追加 */
        val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("""
                    CREATE TABLE IF NOT EXISTS specimen_photos (
                        id         TEXT NOT NULL PRIMARY KEY,
                        filePath   TEXT NOT NULL,
                        locationId TEXT NOT NULL,
                        specimenId TEXT,
                        takenAt    TEXT NOT NULL
                    )
                """.trimIndent())
            }
        }

        /**
         * バージョン2→3:
         *  - specimen_photos に mediaType 列を追加（デフォルト 'photo'）
         *  - custom_field_defs テーブルを追加
         *  - custom_field_values テーブルを追加
         */
        val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(db: SupportSQLiteDatabase) {
                // specimen_photos に mediaType 列を追加
                db.execSQL(
                    "ALTER TABLE specimen_photos ADD COLUMN mediaType TEXT NOT NULL DEFAULT 'photo'"
                )

                // カスタムフィールド定義テーブル
                db.execSQL("""
                    CREATE TABLE IF NOT EXISTS custom_field_defs (
                        id        TEXT NOT NULL PRIMARY KEY,
                        label     TEXT NOT NULL,
                        unit      TEXT NOT NULL,
                        valueType TEXT NOT NULL,
                        appliesTo TEXT NOT NULL,
                        sortOrder INTEGER NOT NULL
                    )
                """.trimIndent())

                // カスタムフィールド値テーブル
                db.execSQL("""
                    CREATE TABLE IF NOT EXISTS custom_field_values (
                        id         TEXT NOT NULL PRIMARY KEY,
                        fieldDefId TEXT NOT NULL,
                        locationId TEXT NOT NULL,
                        specimenId TEXT,
                        value      TEXT NOT NULL
                    )
                """.trimIndent())
            }
        }

        /** バージョン3→4: route_logs / route_points テーブルを追加 */
        val MIGRATION_3_4 = object : Migration(3, 4) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("""
                    CREATE TABLE IF NOT EXISTS route_logs (
                        id                   TEXT NOT NULL PRIMARY KEY,
                        name                 TEXT NOT NULL,
                        startTime            INTEGER NOT NULL,
                        endTime              INTEGER,
                        totalDistanceMeters  REAL NOT NULL DEFAULT 0.0,
                        pointCount           INTEGER NOT NULL DEFAULT 0
                    )
                """.trimIndent())

                db.execSQL("""
                    CREATE TABLE IF NOT EXISTS route_points (
                        id         TEXT NOT NULL PRIMARY KEY,
                        routeLogId TEXT NOT NULL,
                        latitude   REAL NOT NULL,
                        longitude  REAL NOT NULL,
                        altitude   REAL NOT NULL,
                        timestamp  INTEGER NOT NULL,
                        accuracy   REAL NOT NULL
                    )
                """.trimIndent())

                db.execSQL(
                    "CREATE INDEX IF NOT EXISTS index_route_points_routeLogId ON route_points(routeLogId)"
                )
            }
        }

        /**
         * バージョン4→5:
         *  - collection_records に tubeDiameterMm 列を追加
         *    （貼り付け先チューブの外径 mm。null = フラット面。デフォルト NULL）
         */
        val MIGRATION_4_5 = object : Migration(4, 5) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    "ALTER TABLE collection_records ADD COLUMN tubeDiameterMm REAL"
                )
            }
        }
    }
}
