package com.emoon.okr.config;

import com.emoon.okr.service.DatabaseBackupService;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.io.TempDir;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.transaction.support.TransactionTemplate;

import java.nio.file.Files;
import java.nio.file.Path;

import static org.junit.jupiter.api.Assertions.*;

class DatabaseMigrationRunnerTest {

    private JdbcTemplate jdbcTemplate;
    private TransactionTemplate transactionTemplate;
    private DatabaseBackupService backupService;
    private Path tempDir;

    @BeforeEach
    void setUp(@TempDir Path tempDir) throws Exception {
        this.tempDir = tempDir;
        // Create temp db file for backup service to find
        Path dbFile = tempDir.resolve("test.db");
        Files.createFile(dbFile);

        SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
        dataSource.setDriverClassName("org.sqlite.JDBC");
        dataSource.setUrl("jdbc:sqlite::memory:");
        dataSource.setSuppressClose(true);
        jdbcTemplate = new JdbcTemplate(dataSource);
        DataSourceTransactionManager txManager = new DataSourceTransactionManager(dataSource);
        transactionTemplate = new TransactionTemplate(txManager);
        backupService = new DatabaseBackupService(
                "jdbc:sqlite:" + dbFile.toString(),
                tempDir.resolve("backups").toString());
    }

    @Test
    void runAppliesVersionedMigrationsOnlyOnce() {
        createBaseTables();

        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();
        runner.run();

        assertColumnExists(jdbcTemplate, "sys_user", "status");
        assertColumnExists(jdbcTemplate, "performance_score", "published_at");
        assertTableExists(jdbcTemplate, "period_participant");
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 1", Integer.class));
    }

    @Test
    void migrationAddsPeriodStageDatesAndParticipantReportingSnapshot() {
        createBaseTables();

        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();

        assertColumnExists(jdbcTemplate, "assessment_period", "okr_due_date");
        assertColumnExists(jdbcTemplate, "assessment_period", "self_review_due_date");
        assertColumnExists(jdbcTemplate, "period_participant", "department_id_snapshot");
        assertColumnExists(jdbcTemplate, "period_participant", "superior_id_snapshot");
        assertColumnExists(jdbcTemplate, "period_participant", "position_snapshot");
        assertColumnExists(jdbcTemplate, "period_participant", "role_snapshot");
        assertColumnExists(jdbcTemplate, "period_participant", "evaluator_id");
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 5", Integer.class));
    }

    @Test
    void migrationAddsPeerReviewTablesAndDeadlines() {
        createBaseTables();

        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();
        runner.run();

        assertColumnExists(jdbcTemplate, "assessment_period", "peer_invitation_deadline");
        assertColumnExists(jdbcTemplate, "assessment_period", "peer_review_deadline");
        assertTableExists(jdbcTemplate, "peer_review_request");
        assertTableExists(jdbcTemplate, "peer_review_invitation");
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 13", Integer.class));
    }

    @Test
    void migrationAddsPeriodDimensionSnapshotAndExplicitTemplateAssignments() {
        createBaseTables();
        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();

        assertTableExists(jdbcTemplate, "period_dimension");
        assertTableExists(jdbcTemplate, "kpi_template_assignment");
        assertColumnExists(jdbcTemplate, "assessment_period", "company_owner_user_id");
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 8", Integer.class));
    }

    @Test
    void migrationNormalizesLegacyPerformanceTemplatesToFortyPoints() throws Exception {
        createBaseTables();
        jdbcTemplate.execute("""
                CREATE TABLE kpi_template (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(128) NOT NULL,
                    category VARCHAR(64),
                    items_json TEXT,
                    deleted INTEGER NOT NULL DEFAULT 0
                )
                """);
        jdbcTemplate.update("INSERT INTO kpi_template(name, items_json) VALUES (?, ?)",
                "研发工程师", "[{\"name\":\"交付\",\"maxScore\":20},{\"name\":\"质量\",\"maxScore\":15},{\"name\":\"协作\",\"maxScore\":10},{\"name\":\"成长\",\"maxScore\":10},{\"name\":\"创新\",\"maxScore\":5}]");

        new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService).run();

        String json = jdbcTemplate.queryForObject("SELECT items_json FROM kpi_template WHERE id = 1", String.class);
        var items = new com.fasterxml.jackson.databind.ObjectMapper().readTree(json);
        int total = 0;
        for (var item : items) total += item.get("maxScore").asInt();
        assertEquals(40, total);
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 9", Integer.class));
    }

    @Test
    void migrationBackfillsLegacyParticipantEvaluatorAndExemptsUsersWithoutSuperior() {
        createBaseTables();
        jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN superior_id INTEGER");
        jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN department_id INTEGER");
        jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN position VARCHAR(128)");
        jdbcTemplate.update("INSERT INTO sys_user(id,username,password_hash,real_name,role,superior_id,created_at,updated_at) " +
                "VALUES (1,'manager','x','上级','EMPLOYEE',NULL,datetime('now'),datetime('now'))");
        jdbcTemplate.update("INSERT INTO sys_user(id,username,password_hash,real_name,role,superior_id,department_id,position,created_at,updated_at) " +
                "VALUES (2,'employee','x','员工','EMPLOYEE',1,3,'后端开发工程师',datetime('now'),datetime('now'))");
        jdbcTemplate.execute("""
                CREATE TABLE period_participant (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
                    reason TEXT,
                    operated_by INTEGER,
                    exempted_at TEXT,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.update("INSERT INTO period_participant(period_id,user_id,status,created_at,updated_at) " +
                "VALUES (1,1,'ACTIVE',datetime('now'),datetime('now'))");
        jdbcTemplate.update("INSERT INTO period_participant(period_id,user_id,status,created_at,updated_at) " +
                "VALUES (1,2,'ACTIVE',datetime('now'),datetime('now'))");

        new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService).run();

        assertEquals(1L, jdbcTemplate.queryForObject(
                "SELECT evaluator_id FROM period_participant WHERE user_id = 2", Long.class));
        assertEquals("ACTIVE", jdbcTemplate.queryForObject(
                "SELECT status FROM period_participant WHERE user_id = 2", String.class));
        assertEquals("后端开发工程师", jdbcTemplate.queryForObject(
                "SELECT position_snapshot FROM period_participant WHERE user_id = 2", String.class));
        assertEquals(3L, jdbcTemplate.queryForObject(
                "SELECT department_id_snapshot FROM period_participant WHERE user_id = 2", Long.class));
        assertEquals("EMPLOYEE", jdbcTemplate.queryForObject(
                "SELECT role_snapshot FROM period_participant WHERE user_id = 2", String.class));
        assertEquals("EXEMPT_NO_EVALUATOR", jdbcTemplate.queryForObject(
                "SELECT status FROM period_participant WHERE user_id = 1", String.class));
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 10", Integer.class));
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 11", Integer.class));
    }

    @Test
    void migrationAssignsWangkangAsTechnicalDirectorWithoutRewritingExecutingSnapshot() {
        createBaseTables();
        jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN position VARCHAR(128)");
        jdbcTemplate.update("INSERT INTO sys_user(id,username,password_hash,real_name,role,position,created_at,updated_at) " +
                "VALUES (3,'wangkang','x','王康','SUPER_ADMIN','后端开发工程师',datetime('now'),datetime('now'))");
        jdbcTemplate.execute("""
                CREATE TABLE period_participant (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
                    position_snapshot VARCHAR(128),
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.update("INSERT INTO assessment_period(id,name,start_date,end_date,status,created_at,updated_at) " +
                "VALUES (1,'执行周期','2026-07-01','2026-07-31','EXECUTING',datetime('now'),datetime('now'))");
        jdbcTemplate.update("INSERT INTO period_participant(period_id,user_id,status,position_snapshot,created_at,updated_at) " +
                "VALUES (1,3,'ACTIVE','后端开发工程师',datetime('now'),datetime('now'))");

        new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService).run();

        assertEquals("技术总监", jdbcTemplate.queryForObject(
                "SELECT position FROM sys_user WHERE username = 'wangkang'", String.class));
        assertEquals("后端开发工程师", jdbcTemplate.queryForObject(
                "SELECT position_snapshot FROM period_participant WHERE user_id = 3", String.class));
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 12", Integer.class));
    }

    @Test
    void shouldRollbackMigrationAndNotRecordVersionWhenMigrationFails() {
        createBaseTables();

        // Create a custom runner with a failing migration
        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService) {
            @Override
            public void run(String... args) {
                jdbcTemplate.execute("""
                        CREATE TABLE IF NOT EXISTS schema_migration (
                            version INTEGER PRIMARY KEY,
                            description TEXT NOT NULL,
                            applied_at TEXT NOT NULL
                        )
                        """);

                // Apply migration 1 first (succeeds)
                MigrationContext ctx = new MigrationContext(jdbcTemplate);
                transactionTemplate.executeWithoutResult(status -> {
                    jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'");
                    jdbcTemplate.update(
                            "INSERT INTO schema_migration(version, description, applied_at) VALUES (?, ?, datetime('now'))",
                            1, "test migration 1");
                });

                // Now try a failing migration 2
                try {
                    transactionTemplate.executeWithoutResult(status -> {
                        jdbcTemplate.execute("ALTER TABLE sys_user ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'");
                        // Force failure
                        jdbcTemplate.execute("THIS IS INVALID SQL");
                        jdbcTemplate.update(
                                "INSERT INTO schema_migration(version, description, applied_at) VALUES (?, ?, datetime('now'))",
                                2, "should not be recorded");
                    });
                    fail("Expected exception was not thrown");
                } catch (Exception e) {
                    // Expected — migration failed
                }
            }
        };

        try {
            runner.run();
        } catch (Exception e) {
            // May throw RuntimeException wrapping the migration failure
        }

        // Migration 1 should be recorded
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 1", Integer.class));
        // Migration 2 should NOT be recorded
        assertEquals(0, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 2", Integer.class));
        // Column should exist (from migration 1, not rolled back)
        assertColumnExists(jdbcTemplate, "sys_user", "status");
    }

    @Test
    void shouldBackfillCommentsJsonIntoScoreItems() {
        createBaseTables();

        // Insert a score with comments_json
        jdbcTemplate.update(
                "INSERT INTO performance_score(id, period_id, user_id, evaluator_id, type, comments_json, created_at, updated_at) " +
                "VALUES (1, 1, 1, 2, 'SELF', ?, datetime('now'), datetime('now'))",
                "{\"krScores\":[{\"krId\":1,\"title\":\"KR1\",\"score\":0.8},{\"krId\":2,\"title\":\"KR2\",\"score\":0.6}]," +
                "\"dimensions\":[{\"id\":1,\"name\":\"质量\",\"maxScore\":10,\"score\":8},{\"id\":2,\"name\":\"效率\",\"maxScore\":10,\"score\":7}]}");

        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();

        // Verify performance_score_item was populated
        int krCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM performance_score_item WHERE score_id = 1 AND item_type = 'KR'", Integer.class);
        assertEquals(2, krCount, "Should have 2 KR items");

        int dimCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM performance_score_item WHERE score_id = 1 AND item_type = 'DIMENSION'", Integer.class);
        assertEquals(2, dimCount, "Should have 2 dimension items");

        // Verify original comments_json is preserved
        String json = jdbcTemplate.queryForObject(
                "SELECT comments_json FROM performance_score WHERE id = 1", String.class);
        assertNotNull(json);
        assertTrue(json.contains("krScores"));

        // Verify migrations recorded
        assertEquals(1, jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM schema_migration WHERE version = 3", Integer.class));
    }

    private void createBaseTables() {
        jdbcTemplate.execute("""
                CREATE TABLE sys_user (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username VARCHAR(64) NOT NULL UNIQUE,
                    password_hash VARCHAR(256) NOT NULL,
                    real_name VARCHAR(64) NOT NULL,
                    role VARCHAR(20) NOT NULL DEFAULT 'EMPLOYEE',
                    deleted INTEGER NOT NULL DEFAULT 0,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE assessment_period (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(128) NOT NULL,
                    period_type VARCHAR(20) NOT NULL DEFAULT 'MONTHLY',
                    start_date TEXT NOT NULL,
                    end_date TEXT NOT NULL,
                    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE performance_score (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    evaluator_id INTEGER NOT NULL,
                    type VARCHAR(20) NOT NULL,
                    comments_json TEXT,
                    deleted INTEGER NOT NULL DEFAULT 0,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE performance_feedback (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER NOT NULL,
                    user_id INTEGER NOT NULL,
                    superior_id INTEGER NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE okr_objective (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period_id INTEGER NOT NULL,
                    user_id INTEGER,
                    dept_id INTEGER,
                    title VARCHAR(256) NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE kr_key_result (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    objective_id INTEGER NOT NULL,
                    title VARCHAR(256) NOT NULL,
                    source_kr_id INTEGER,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.execute("""
                CREATE TABLE notification (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    type VARCHAR(32) NOT NULL,
                    title VARCHAR(256) NOT NULL,
                    is_read INTEGER DEFAULT 0,
                    created_at TEXT NOT NULL
                )
                """);
    }

    private void assertColumnExists(JdbcTemplate jdbcTemplate, String tableName, String columnName) {
        boolean exists = jdbcTemplate.queryForList("PRAGMA table_info(" + tableName + ")").stream()
                .anyMatch(row -> columnName.equalsIgnoreCase(String.valueOf(row.get("name"))));
        assertTrue(exists, tableName + "." + columnName + " should exist");
    }


    @Test
    void shouldMigrateLegacy4x15DimensionsTo4x10() {
        createBaseTables();
        jdbcTemplate.execute("""
                CREATE TABLE assessment_dimension (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(64) NOT NULL,
                    max_score INTEGER NOT NULL DEFAULT 10,
                    sort_order INTEGER DEFAULT 0,
                    deleted INTEGER NOT NULL DEFAULT 0,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )
                """);
        jdbcTemplate.update("INSERT INTO assessment_dimension(id,name,max_score,sort_order,created_at,updated_at) VALUES (1,\"交付质量\",15,1,datetime(\"now\"),datetime(\"now\"))");
        jdbcTemplate.update("INSERT INTO assessment_dimension(id,name,max_score,sort_order,created_at,updated_at) VALUES (2,\"协作沟通\",15,2,datetime(\"now\"),datetime(\"now\"))");
        jdbcTemplate.update("INSERT INTO assessment_dimension(id,name,max_score,sort_order,created_at,updated_at) VALUES (3,\"学习成长\",15,3,datetime(\"now\"),datetime(\"now\"))");
        jdbcTemplate.update("INSERT INTO assessment_dimension(id,name,max_score,sort_order,created_at,updated_at) VALUES (4,\"创新贡献\",15,4,datetime(\"now\"),datetime(\"now\"))");

        DatabaseMigrationRunner runner = new DatabaseMigrationRunner(jdbcTemplate, transactionTemplate, backupService);
        runner.run();

        for (int i = 1; i <= 4; i++) {
            int maxScore = jdbcTemplate.queryForObject(
                "SELECT max_score FROM assessment_dimension WHERE id = " + i, Integer.class);
            assertEquals(10, maxScore, "Dim " + i + " should be 10");
        }

        assertEquals(1, jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM schema_migration WHERE version = 4", Integer.class));
    }

    private void assertTableExists(JdbcTemplate jdbcTemplate, String tableName) {
        Integer count = jdbcTemplate.queryForObject("""
                SELECT COUNT(*) FROM sqlite_master
                WHERE type = 'table' AND name = ?
                """, Integer.class, tableName);
        assertEquals(1, count);
    }
}