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


REQUEST_COLUMNS = [
    ("book_spec_uid", "VARCHAR(100) DEFAULT NULL AFTER design_name"),
    ("cover_template_uid", "VARCHAR(100) DEFAULT NULL AFTER book_spec_uid"),
    ("content_template_uid", "VARCHAR(100) DEFAULT NULL AFTER cover_template_uid"),
    ("divider_template_uid", "VARCHAR(100) DEFAULT NULL AFTER content_template_uid"),
    ("publish_template_uid", "VARCHAR(100) DEFAULT NULL AFTER divider_template_uid"),
    ("cover_image_url", "TEXT DEFAULT NULL AFTER selected_preview_urls"),
    ("pdf_status", "VARCHAR(30) NOT NULL DEFAULT 'not_purchased' AFTER pdf_addon"),
    ("pdf_cover_url", "VARCHAR(500) DEFAULT NULL AFTER pdf_status"),
    ("pdf_contents_url", "VARCHAR(500) DEFAULT NULL AFTER pdf_cover_url"),
    ("pdf_ready_at", "DATETIME DEFAULT NULL AFTER pdf_contents_url"),
    ("pdf_last_checked_at", "DATETIME DEFAULT NULL AFTER pdf_ready_at"),
    ("pdf_last_error", "VARCHAR(500) DEFAULT NULL AFTER pdf_last_checked_at"),
    ("shipping_recipient_name", "VARCHAR(100) DEFAULT NULL AFTER admin_memo"),
    ("shipping_recipient_phone", "VARCHAR(20) DEFAULT NULL AFTER shipping_recipient_name"),
    ("shipping_postal_code", "VARCHAR(10) DEFAULT NULL AFTER shipping_recipient_phone"),
    ("shipping_address1", "VARCHAR(200) DEFAULT NULL AFTER shipping_postal_code"),
    ("shipping_address2", "VARCHAR(200) DEFAULT NULL AFTER shipping_address1"),
    ("shipping_memo", "VARCHAR(200) DEFAULT NULL AFTER shipping_address2"),
    ("sweetbook_book_uid", "VARCHAR(100) DEFAULT NULL AFTER shipping_memo"),
    ("sweetbook_order_uid", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_book_uid"),
    ("sweetbook_order_status", "VARCHAR(50) DEFAULT NULL AFTER sweetbook_order_uid"),
    ("sweetbook_order_status_display", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_order_status"),
    ("sweetbook_item_uid", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_order_status_display"),
    ("sweetbook_item_status", "VARCHAR(50) DEFAULT NULL AFTER sweetbook_item_uid"),
    ("sweetbook_item_status_display", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_item_status"),
    ("sweetbook_response", "LONGTEXT DEFAULT NULL AFTER sweetbook_item_status_display"),
    ("sweetbook_last_error", "VARCHAR(500) DEFAULT NULL AFTER sweetbook_response"),
    ("cancelled_at", "DATETIME DEFAULT NULL AFTER sweetbook_last_error"),
    ("refunded_at", "DATETIME DEFAULT NULL AFTER cancelled_at"),
]


PAYMENT_COLUMNS = [
    ("book_spec_uid", "VARCHAR(100) DEFAULT NULL AFTER design_name"),
    ("cover_template_uid", "VARCHAR(100) DEFAULT NULL AFTER book_spec_uid"),
    ("content_template_uid", "VARCHAR(100) DEFAULT NULL AFTER cover_template_uid"),
    ("divider_template_uid", "VARCHAR(100) DEFAULT NULL AFTER content_template_uid"),
    ("publish_template_uid", "VARCHAR(100) DEFAULT NULL AFTER divider_template_uid"),
    ("cover_image_url", "TEXT DEFAULT NULL AFTER selected_preview_urls"),
    ("pdf_status", "VARCHAR(30) NOT NULL DEFAULT 'not_purchased' AFTER pdf_addon"),
    ("pdf_cover_url", "VARCHAR(500) DEFAULT NULL AFTER pdf_status"),
    ("pdf_contents_url", "VARCHAR(500) DEFAULT NULL AFTER pdf_cover_url"),
    ("pdf_ready_at", "DATETIME DEFAULT NULL AFTER pdf_contents_url"),
    ("pdf_last_checked_at", "DATETIME DEFAULT NULL AFTER pdf_ready_at"),
    ("pdf_last_error", "VARCHAR(500) DEFAULT NULL AFTER pdf_last_checked_at"),
    ("shipping_recipient_name", "VARCHAR(100) DEFAULT NULL AFTER toss_response"),
    ("shipping_recipient_phone", "VARCHAR(20) DEFAULT NULL AFTER shipping_recipient_name"),
    ("shipping_postal_code", "VARCHAR(10) DEFAULT NULL AFTER shipping_recipient_phone"),
    ("shipping_address1", "VARCHAR(200) DEFAULT NULL AFTER shipping_postal_code"),
    ("shipping_address2", "VARCHAR(200) DEFAULT NULL AFTER shipping_address1"),
    ("shipping_memo", "VARCHAR(200) DEFAULT NULL AFTER shipping_address2"),
    ("sweetbook_book_uid", "VARCHAR(100) DEFAULT NULL AFTER shipping_memo"),
    ("sweetbook_order_uid", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_book_uid"),
    ("sweetbook_order_status", "VARCHAR(50) DEFAULT NULL AFTER sweetbook_order_uid"),
    ("sweetbook_order_status_display", "VARCHAR(100) DEFAULT NULL AFTER sweetbook_order_status"),
    ("sweetbook_response", "LONGTEXT DEFAULT NULL AFTER sweetbook_order_status_display"),
    ("sweetbook_last_error", "VARCHAR(500) DEFAULT NULL AFTER sweetbook_response"),
]


CREATE_LOGS_TABLE = """
CREATE TABLE IF NOT EXISTS babynote_kidsnote_sweetbook_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  request_id VARCHAR(36) DEFAULT NULL,
  payment_order_id VARCHAR(36) DEFAULT NULL,
  sweetbook_order_uid VARCHAR(100) DEFAULT NULL,
  event_type VARCHAR(80) NOT NULL,
  method VARCHAR(10) DEFAULT NULL,
  endpoint VARCHAR(255) DEFAULT NULL,
  status_code INT DEFAULT NULL,
  success BOOLEAN NOT NULL DEFAULT FALSE,
  request_payload LONGTEXT DEFAULT NULL,
  response_payload LONGTEXT DEFAULT NULL,
  error_message VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_request_id (request_id),
  INDEX idx_payment_order_id (payment_order_id),
  INDEX idx_sweetbook_order_uid (sweetbook_order_uid),
  INDEX idx_event_type (event_type),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


CREATE_WEBHOOKS_TABLE = """
CREATE TABLE IF NOT EXISTS babynote_kidsnote_sweetbook_webhooks (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  delivery_id VARCHAR(100) NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  sweetbook_order_uid VARCHAR(100) DEFAULT NULL,
  payload LONGTEXT NOT NULL,
  processed_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_delivery_id (delivery_id),
  INDEX idx_event_type (event_type),
  INDEX idx_sweetbook_order_uid (sweetbook_order_uid),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


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 run():
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        cursor = connection.cursor()
        print("DB Connected")

        for column_name, definition in REQUEST_COLUMNS:
            add_column(cursor, "babynote_kidsnote_requests", column_name, definition)
        add_index(cursor, "babynote_kidsnote_requests", "INDEX idx_sweetbook_order_uid (sweetbook_order_uid)")

        for column_name, definition in PAYMENT_COLUMNS:
            add_column(cursor, "babynote_kidsnote_payment_orders", column_name, definition)
        add_index(cursor, "babynote_kidsnote_payment_orders", "INDEX idx_sweetbook_order_uid (sweetbook_order_uid)")

        cursor.execute(CREATE_LOGS_TABLE)
        print("OK: create babynote_kidsnote_sweetbook_logs")
        cursor.execute(CREATE_WEBHOOKS_TABLE)
        print("OK: create babynote_kidsnote_sweetbook_webhooks")

        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()