import sys
import pymysql

DB_CONFIG = dict(
    host="localhost",
    port=3306,
    user="root",
    password="",
    db="cems_db",
    charset="utf8mb4",
)

STATEMENTS = [
    # 1. Create cems_sequence table
    """
    CREATE TABLE IF NOT EXISTS cems_sequence (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        sequence_prefix VARCHAR(20) NOT NULL DEFAULT 'INV',
        module_code     VARCHAR(10) NOT NULL,
        fiscal_year     VARCHAR(20) NOT NULL DEFAULT '2083',
        sequence_number INT NOT NULL DEFAULT 0,
        UNIQUE KEY uq_prefix_module_fy (sequence_prefix, module_code, fiscal_year)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """,
    # 2. Seed initial sequence records (PG, ST, TK, MT) with fiscal year 2083
    """
    INSERT IGNORE INTO cems_sequence (sequence_prefix, module_code, fiscal_year, sequence_number) VALUES
        ('INV', 'PG', '2083', 0),
        ('INV', 'ST', '2083', 0),
        ('INV', 'TK', '2083', 0),
        ('INV', 'MT', '2083', 0)
    """
]

def add_invoice_number_column(conn):
    """Add invoice_number to cems_payment only if it does not already exist."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = 'cems_payment' AND COLUMN_NAME = 'invoice_number'
        """, ("cems_db",))
        (exists,) = cur.fetchone()

    if exists:
        print("  [OK] invoice_number column already exists in cems_payment — skipping ALTER TABLE")
        return

    with conn.cursor() as cur:
        cur.execute("""
            ALTER TABLE cems_payment
                ADD COLUMN invoice_number VARCHAR(100)
        """)
    print("  [OK] Added invoice_number column to cems_payment table")

def main():
    print("=== CEMS Sequence and Payment Migration ===\n")
    try:
        conn = pymysql.connect(**DB_CONFIG)
        print("[OK] Connected to MySQL (cems_db)\n")
    except Exception as e:
        print(f"[FAIL] Connection failed: {e}")
        sys.exit(1)

    with conn:
        conn.autocommit(False)
        try:
            # 1. Create table
            with conn.cursor() as cur:
                cur.execute(STATEMENTS[0])
            print("  [OK] Step 1: Created cems_sequence table")

            # 2. Add column to cems_payment
            add_invoice_number_column(conn)

            # 3. Seed sequence records
            with conn.cursor() as cur:
                cur.execute(STATEMENTS[1])
            print("  [OK] Step 3: Seeded cems_sequence table with 2083 fiscal year records")

            conn.commit()
            print("\n[DONE] Sequence migration completed successfully!")

        except Exception as e:
            conn.rollback()
            print(f"\n[FAIL] Sequence migration failed: {e}")
            sys.exit(1)

if __name__ == "__main__":
    main()