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_USAGE_TABLE = """
CREATE TABLE IF NOT EXISTS calmoa_creation_usage (
  device_serial VARCHAR(255) PRIMARY KEY,
  total_created_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

CREATE_GRANTS_TABLE = """
CREATE TABLE IF NOT EXISTS calmoa_ad_creation_grants (
  token VARCHAR(36) PRIMARY KEY,
  device_serial VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_device_serial (device_serial),
  INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

SEED_EXISTING_USAGE = """
INSERT INTO calmoa_creation_usage (device_serial, total_created_count)
SELECT device_serial, COUNT(*)
FROM calmoa_appointments
WHERE device_serial IS NOT NULL AND device_serial != ''
GROUP BY device_serial
ON DUPLICATE KEY UPDATE
  total_created_count = GREATEST(total_created_count, VALUES(total_created_count))
"""


def run():
    connection = mysql.connector.connect(**DB_CONFIG)
    cursor = connection.cursor()
    try:
        cursor.execute(CREATE_USAGE_TABLE)
        cursor.execute(CREATE_GRANTS_TABLE)
        cursor.execute(SEED_EXISTING_USAGE)
        connection.commit()
        print("OK: Calmoa creation quota tables created and existing usage seeded")
    finally:
        cursor.close()
        connection.close()


if __name__ == "__main__":
    run()