import argparse
import json
import os
from pathlib import Path

import pymysql
from werkzeug.security import generate_password_hash


BASE_DIR = Path(__file__).resolve().parent


def load_db_config():
    env = os.environ
    if env.get("DB_HOST"):
        return {
            "host": env.get("DB_HOST"),
            "user": env.get("DB_USER"),
            "password": env.get("DB_PASSWORD"),
            "database": env.get("DB_NAME"),
            "port": int(env.get("DB_PORT", "3306")),
        }
    for candidate in (BASE_DIR / "db_config.json", BASE_DIR.parent / "db_config.json"):
        if candidate.exists():
            data = json.loads(candidate.read_text(encoding="utf-8"))
            return {
                "host": data["host"],
                "user": data["user"],
                "password": data["password"],
                "database": data["database"],
                "port": int(data.get("port", 3306)),
            }
    raise RuntimeError("DB config not found. Set DB_* env vars or db_config.json.")


def connect():
    cfg = load_db_config()
    return pymysql.connect(
        host=cfg["host"],
        user=cfg["user"],
        password=cfg["password"],
        database=cfg["database"],
        port=cfg["port"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=False,
    )


def ensure_schema(conn):
    with conn.cursor() as cur:
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(100) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                name VARCHAR(150) DEFAULT '',
                email VARCHAR(255) DEFAULT '',
                role VARCHAR(100) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB;
            """
        )
        cur.execute("SHOW COLUMNS FROM users")
        columns = {row["Field"] for row in cur.fetchall()}
        if "name" not in columns:
            cur.execute("ALTER TABLE users ADD COLUMN name VARCHAR(150) DEFAULT ''")
        if "email" not in columns:
            cur.execute("ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT ''")

        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS user_storage (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                storage_key VARCHAR(200) NOT NULL,
                storage_value LONGTEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uk_user_storage (user_id, storage_key)
            ) ENGINE=InnoDB;
            """
        )
        conn.commit()


def load_seed_data(path: Path):
    if not path.exists():
        raise FileNotFoundError(f"Seed file not found: {path}")
    return json.loads(path.read_text(encoding="utf-8"))


def upsert_user(cur, data):
    username = (data.get("username") or "").strip()
    if not username:
        raise ValueError("User missing username.")
    password = data.get("password")
    name = (data.get("name") or "").strip()
    email = (data.get("email") or "").strip()
    role = (data.get("role") or "Atendimento").strip()

    cur.execute("SELECT id FROM users WHERE username = %s", (username,))
    row = cur.fetchone()
    if row:
        if password:
            cur.execute(
                """
                UPDATE users
                SET password = %s, name = %s, email = %s, role = %s
                WHERE id = %s
                """,
                (generate_password_hash(password), name, email, role, row["id"]),
            )
        else:
            cur.execute(
                """
                UPDATE users
                SET name = %s, email = %s, role = %s
                WHERE id = %s
                """,
                (name, email, role, row["id"]),
            )
        return row["id"]

    if not password:
        raise ValueError(f"User {username} missing password.")

    cur.execute(
        """
        INSERT INTO users (username, password, name, email, role)
        VALUES (%s, %s, %s, %s, %s)
        """,
        (username, generate_password_hash(password), name, email, role),
    )
    return cur.lastrowid


def apply_storage(cur, user_id, storage, clear_first=False):
    if clear_first:
        cur.execute("DELETE FROM user_storage WHERE user_id = %s", (user_id,))
    for key, value in storage.items():
        if not isinstance(value, str):
            value = json.dumps(value, ensure_ascii=False)
        cur.execute(
            """
            INSERT INTO user_storage (user_id, storage_key, storage_value)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE storage_value = VALUES(storage_value)
            """,
            (user_id, key, value),
        )


def main():
    parser = argparse.ArgumentParser(description="Seed initial data for PrintLab web app.")
    parser.add_argument("--file", default="seed_data.json", help="Path to seed JSON file.")
    parser.add_argument("--clear-storage", action="store_true", help="Clear user storage before inserting.")
    args = parser.parse_args()

    seed_path = Path(args.file)
    if not seed_path.is_absolute():
        seed_path = BASE_DIR / seed_path

    data = load_seed_data(seed_path)
    users = data.get("users") or []

    conn = connect()
    try:
        ensure_schema(conn)
        with conn.cursor() as cur:
            for user_data in users:
                user_id = upsert_user(cur, user_data)
                storage = user_data.get("storage")
                if storage and isinstance(storage, dict):
                    apply_storage(cur, user_id, storage, clear_first=args.clear_storage)
        conn.commit()
    finally:
        conn.close()

    print("Seed concluido com sucesso.")


if __name__ == "__main__":
    main()
