import json
import os

import mysql.connector
from mysql.connector import Error


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"),
}

DELETION_REASON = "미리보기 유효기간 만료"


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


def normalize_notification_type(cursor):
    cursor.execute(
        "ALTER TABLE notifications "
        "MODIFY COLUMN type VARCHAR(50) NOT NULL COMMENT '알림 타입'"
    )
    print("OK: notifications.type changed to VARCHAR(50)")


def build_audit_payload(raw_payload):
    try:
        draft = json.loads(raw_payload) if isinstance(raw_payload, str) else raw_payload
    except (TypeError, ValueError):
        draft = {}
    if not isinstance(draft, dict):
        draft = {}

    diary_ids = draft.get("selectedDiaryIds")
    preview_urls = draft.get("selectedPreviewUrls")
    diary_ids = diary_ids if isinstance(diary_ids, list) else []
    preview_urls = preview_urls if isinstance(preview_urls, list) else []
    return {
        "deleted": True,
        "deletionReason": DELETION_REASON,
        "designCode": draft.get("designCode") or "",
        "designName": draft.get("designName") or draft.get("orderName") or "",
        "productCode": draft.get("productCode") or "",
        "bookSpecUid": draft.get("bookSpecUid") or "",
        "periodType": draft.get("periodType") or "",
        "periodLabel": draft.get("periodLabel") or "",
        "startDate": draft.get("startDate") or "",
        "endDate": draft.get("endDate") or "",
        "diaryCount": draft.get("diaryCount") or len(diary_ids),
        "imageCount": draft.get("imageCount") or len(preview_urls),
        "estimatedPages": draft.get("estimatedPages") or 0,
        "coverType": draft.get("coverType") or "",
        "pdfAddon": bool(draft.get("pdfAddon")),
        "totalPrice": draft.get("totalPrice") or 0,
    }


def purge_expired_previews(cursor):
    cursor.execute(
        """
        SELECT preview.id, preview.draft_payload
          FROM babynote_kidsnote_previews preview
         WHERE preview.expires_at <= NOW()
           AND preview.status NOT IN ('deleted', 'cancelled')
           AND NOT EXISTS (
             SELECT 1
               FROM babynote_kidsnote_payment_orders payment_order
              WHERE payment_order.preview_id = preview.id
                AND (
                  LOWER(COALESCE(payment_order.status, '')) = 'paid'
                  OR (
                    LOWER(COALESCE(payment_order.status, '')) = 'pending'
                    AND payment_order.updated_at >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
                  )
                )
           )
        """
    )
    previews = cursor.fetchall()
    for preview_id, draft_payload in previews:
        audit_payload = json.dumps(
            build_audit_payload(draft_payload), ensure_ascii=False, separators=(",", ":")
        )
        cursor.execute(
            """
            UPDATE babynote_kidsnote_previews
               SET draft_payload = %s,
                   status = 'deleted',
                   pdf_status = 'deleted',
                   deletion_reason = %s,
                   deleted_at = NOW(),
                   expires_at = LEAST(expires_at, NOW()),
                   updated_at = CURRENT_TIMESTAMP
             WHERE id = %s
            """,
            (audit_payload, DELETION_REASON, preview_id),
        )
        cursor.execute(
            """
            UPDATE babynote_kidsnote_payment_orders
               SET status = 'cancelled',
                   selected_diary_ids = '[]',
                   page_plan = '[]',
                   selected_preview_urls = '[]',
                   cover_image_url = NULL,
                   payment_key = NULL,
                   toss_response = NULL,
                   shipping_recipient_name = NULL,
                   shipping_recipient_phone = NULL,
                   shipping_postal_code = NULL,
                   shipping_address1 = NULL,
                   shipping_address2 = NULL,
                   shipping_memo = NULL,
                   fail_reason = %s,
                   updated_at = CURRENT_TIMESTAMP
             WHERE preview_id = %s
               AND LOWER(COALESCE(status, '')) <> 'paid'
            """,
            (DELETION_REASON, preview_id),
        )
        cursor.execute(
            """
            UPDATE babynote_kidsnote_sweetbook_jobs
               SET status = 'failed',
                   lock_token = NULL,
                   locked_at = NULL,
                   last_error = %s,
                   processed_at = COALESCE(processed_at, NOW()),
                   updated_at = CURRENT_TIMESTAMP
             WHERE request_id = %s
               AND job_type = 'create_preview'
               AND status <> 'completed'
            """,
            (DELETION_REASON, preview_id),
        )
        cursor.execute(
            """
            UPDATE babynote_kidsnote_sweetbook_logs
               SET request_payload = NULL,
                   response_payload = NULL
             WHERE request_id = %s
                OR payment_order_id IN (
                  SELECT payment_order.id
                    FROM babynote_kidsnote_payment_orders payment_order
                   WHERE payment_order.preview_id = %s
                )
            """,
            (preview_id, preview_id),
        )
    print(f"OK: purged {len(previews)} expired unpaid previews")


def run():
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor()
        add_column(cursor, "deletion_reason", "VARCHAR(255) DEFAULT NULL AFTER previewed_at")
        add_column(cursor, "deleted_at", "DATETIME DEFAULT NULL AFTER deletion_reason")
        normalize_notification_type(cursor)
        purge_expired_previews(cursor)
        connection.commit()
        print("Kidsnote preview expiry migration completed")
    finally:
        if cursor is not None:
            cursor.close()
        if connection is not None and connection.is_connected():
            connection.close()


if __name__ == "__main__":
    run()