"""
FreeFlow 정기결제 시스템 - DB 스키마 변경
- subscriptions 테이블에 billing 관련 컬럼 추가
- billing_history 테이블 생성
"""
import mysql.connector

DB_CONFIG = {
    'host': 'officialsite.kr',
    'port': 23306,
    'user': 'admin',
    'password': 'dlgks~123',
    'database': 'freeflow',
}


def main():
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 1. subscriptions 테이블에 billing 컬럼 추가
    columns_to_add = [
        ("billing_anchor_day", "INT DEFAULT 1"),
        ("next_billing_date", "DATE NULL"),
        ("pending_plan_id", "INT NULL"),
        ("last_billing_status", "VARCHAR(20) DEFAULT 'none'"),
    ]

    for col_name, col_def in columns_to_add:
        try:
            cursor.execute(f"ALTER TABLE subscriptions ADD COLUMN {col_name} {col_def}")
            print(f"  [OK] Added column: {col_name}")
        except mysql.connector.Error as e:
            if e.errno == 1060:  # Duplicate column
                print(f"  [SKIP] Column {col_name} already exists")
            else:
                raise

    # 2. billing_history 테이블 생성
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS billing_history (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            type VARCHAR(20) NOT NULL COMMENT 'plan, addon, upgrade_proration',
            reference_id INT NULL COMMENT 'plan.id or addon.id',
            amount INT NOT NULL COMMENT '결제 금액 (원)',
            status VARCHAR(20) NOT NULL COMMENT 'success, failed, pending',
            toss_order_id VARCHAR(100) NULL,
            toss_payment_key VARCHAR(200) NULL,
            description VARCHAR(500) NULL,
            period_start DATE NULL,
            period_end DATE NULL,
            created_at DATETIME DEFAULT NOW(),
            INDEX idx_user_created (user_id, created_at),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """)
    print("[OK] billing_history table created")

    conn.commit()
    cursor.close()
    conn.close()
    print("\n[DONE] All schema changes completed!")


if __name__ == '__main__':
    main()