import os
import mysql.connector


DB_CONFIG = {
    "host": os.environ.get("DB_HOST", "officialsite.kr"),
    "port": int(os.environ.get("DB_PORT", "23306")),
    "user": os.environ.get("DB_USER", "admin"),
    "password": os.environ.get("DB_PASSWORD", "dlgks~123"),
    "database": os.environ.get("DB_NAME", "app_master"),
}


CREATE_PREVIEWS_TABLE = """
CREATE TABLE IF NOT EXISTS babynote_kidsnote_previews (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  draft_fingerprint CHAR(64) NOT NULL,
  draft_payload LONGTEXT NOT NULL,
  sweetbook_book_uid VARCHAR(100) DEFAULT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'queued',
  pdf_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  last_error VARCHAR(500) DEFAULT NULL,
  previewed_at DATETIME DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_fingerprint (user_id, draft_fingerprint),
  INDEX idx_status_updated (status, updated_at),
  INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

KIDSNOTE_PREVIEW_EXPIRY_HOURS = 24


def add_column(cursor, table, column_name, definition):
    try:
        cursor.execute(f"ALTER TABLE {table} ADD COLUMN {column_name} {definition}")
        print(f"OK: add {table}.{column_name}")
    except mysql.connector.Error as error:
        if error.errno == 1060:
            print(f"SKIP: {table}.{column_name} already exists")
            return
        raise


def add_index(cursor, table, index_sql):
    try:
        cursor.execute(f"ALTER TABLE {table} ADD {index_sql}")
        print(f"OK: add {table} {index_sql}")
    except mysql.connector.Error as error:
        if error.errno == 1061:
            print(f"SKIP: {table} {index_sql} already exists")
            return
        raise


def restore_preview_expiry_policy(cursor):
    cursor.execute(
        """
        UPDATE babynote_kidsnote_previews preview
        LEFT JOIN babynote_kidsnote_payment_orders payment_order
          ON payment_order.preview_id = preview.id
         AND LOWER(COALESCE(payment_order.status, '')) = 'paid'
           SET preview.expires_at = DATE_ADD(NOW(), INTERVAL 24 HOUR)
         WHERE preview.status IN ('queued', 'creating', 'rendering', 'ready', 'failed')
           AND payment_order.id IS NULL
           AND preview.expires_at >= '2099-01-01 00:00:00'
        """,
    )
    print(
        f"OK: restored {cursor.rowcount} unpaid kidsnote previews "
        f"to {KIDSNOTE_PREVIEW_EXPIRY_HOURS}-hour expiry"
    )


def run():
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor()
        print("DB Connected")

        cursor.execute(CREATE_PREVIEWS_TABLE)
        print("OK: create babynote_kidsnote_previews")
        add_column(cursor, "babynote_kidsnote_payment_orders", "preview_id", "VARCHAR(36) DEFAULT NULL AFTER id")
        add_index(cursor, "babynote_kidsnote_payment_orders", "INDEX idx_preview_id (preview_id)")
        restore_preview_expiry_policy(cursor)

        connection.commit()
        print("Migration complete")
    finally:
        if cursor is not None:
            cursor.close()
        if connection is not None and connection.is_connected():
            connection.close()
            print("DB connection closed")


if __name__ == "__main__":
    run()