"""
Seed development database with a Green Hills Cemetery tenant and one user per RBAC role.
Idempotent — safe to run multiple times (skips records that already exist).

Uses raw SQL to avoid SQLAlchemy ORM relationship resolution, which requires all
models to be present (some may not exist yet during early development).

Usage:
    # With Docker:
    docker-compose exec api python scripts/seed_dev_data.py

    # Without Docker (local venv, from indelis-api/):
    python3 scripts/seed_dev_data.py
"""
import asyncio
import sys
import os

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

import bcrypt as _bcrypt

from src.core.config import settings


def hash_password(password: str) -> str:
    return _bcrypt.hashpw(password.encode(), _bcrypt.gensalt()).decode()


TENANT = {
    "organization_name": "Green Hills Cemetery",
    "subdomain": "green-hills",
    "contact_email": "admin@green-hills.com",
    "status": "active",
    "plan": "professional",
}

USERS = [
    {
        "email": "site-admin@indelis.com",
        "first_name": "Site Admin",
        "last_name": "User",
        "role": "site_admin",
        "tenant_scoped": False,
    },
    {
        "email": "admin@green-hills.com",
        "first_name": "Administrator",
        "last_name": "User",
        "role": "administrator",
        "tenant_scoped": True,
    },
    {
        "email": "manager@green-hills.com",
        "first_name": "Manager",
        "last_name": "User",
        "role": "manager",
        "tenant_scoped": True,
    },
    {
        "email": "sales@green-hills.com",
        "first_name": "Sales Staff",
        "last_name": "User",
        "role": "sales_staff",
        "tenant_scoped": True,
    },
    {
        "email": "field@green-hills.com",
        "first_name": "Field Staff",
        "last_name": "User",
        "role": "field_staff",
        "tenant_scoped": True,
    },
    {
        "email": "viewer@green-hills.com",
        "first_name": "View Only",
        "last_name": "User",
        "role": "view_only",
        "tenant_scoped": True,
    },
]

PASSWORD = "Admin@12345"


async def seed():
    db_url = settings.DATABASE_URL
    if not db_url.startswith("postgresql+psycopg://"):
        db_url = "postgresql+psycopg://" + db_url.split("://", 1)[-1]

    engine = create_async_engine(db_url, echo=False)
    SessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with SessionLocal() as db:
        # --- Tenant ---
        result = await db.execute(
            text("SELECT id FROM accounts WHERE subdomain = :subdomain"),
            {"subdomain": TENANT["subdomain"]},
        )
        row = result.fetchone()

        if row:
            account_id = str(row[0])
            print(f"[skip] Tenant '{TENANT['subdomain']}' already exists (id={account_id})")
        else:
            result = await db.execute(
                text("""
                    INSERT INTO accounts
                        (organization_name, subdomain, contact_email, status, plan, created_at, updated_at)
                    VALUES
                        (:organization_name, :subdomain, :contact_email, :status, :plan, NOW(), NOW())
                    RETURNING id
                """),
                TENANT,
            )
            account_id = str(result.fetchone()[0])
            print(f"[created] Tenant '{TENANT['subdomain']}' (id={account_id})")

        # --- Users ---
        password_hash = hash_password(PASSWORD)
        created = []
        skipped = []

        for spec in USERS:
            tenant_id = account_id if spec["tenant_scoped"] else None

            if tenant_id:
                check = await db.execute(
                    text("""
                        SELECT id FROM users
                        WHERE email = :email AND tenant_id = :tenant_id AND deleted_at IS NULL
                    """),
                    {"email": spec["email"], "tenant_id": tenant_id},
                )
            else:
                check = await db.execute(
                    text("""
                        SELECT id FROM users
                        WHERE email = :email AND tenant_id IS NULL AND deleted_at IS NULL
                    """),
                    {"email": spec["email"]},
                )

            if check.fetchone():
                skipped.append(spec)
                continue

            await db.execute(
                text("""
                    INSERT INTO users
                        (tenant_id, email, password_hash, first_name, last_name, role, status, created_at, updated_at)
                    VALUES
                        (:tenant_id, :email, :password_hash, :first_name, :last_name, :role, 'active', NOW(), NOW())
                """),
                {
                    "tenant_id": tenant_id,
                    "email": spec["email"],
                    "password_hash": password_hash,
                    "first_name": spec["first_name"],
                    "last_name": spec["last_name"],
                    "role": spec["role"],
                },
            )
            created.append(spec)

        await db.commit()

        for spec in skipped:
            print(f"[skip] User '{spec['email']}' ({spec['role']}) already exists")
        for spec in created:
            print(f"[created] User '{spec['email']}' ({spec['role']})")

        # --- Summary ---
        print()
        print("=" * 64)
        print(" DEV CREDENTIALS — Green Hills Cemetery")
        print("=" * 64)
        print(f" Tenant subdomain : green-hills")
        print(f" API login URL    : POST /api/v1/auth/login")
        print(f"                    Header: X-Tenant-Slug: green-hills")
        print(f"                    (site_admin uses no tenant header)")
        print(f" Password (all)   : {PASSWORD}")
        print("-" * 64)
        print(f" {'Role':<15} {'Email'}")
        print(f" {'-'*15} {'-'*36}")
        for spec in USERS:
            print(f" {spec['role']:<15} {spec['email']}")
        print("=" * 64)

    await engine.dispose()


if __name__ == "__main__":
    asyncio.run(seed())