"""
Create the two plan-sandbox organizations used to test the product as a real
customer sees it.

Both are provisioned in the "just finished connecting Addons, nothing tracked
yet" state:
  - the platform Addons API key (ADDONS_API_KEY from .env) is stored encrypted,
    so the sandbox can talk to Addons without re-pasting a key
  - NO addons_products rows — no modules are selected/tracked yet
  - trial_ends_at is NULL, so a 'free' org really behaves as free rather than
    being uplifted to MAX by the signup trial (see plan_service.get_effective_plan)
  - onboarding_complete stays False, which keeps the Celery sync out (it only
    picks schemas WHERE onboarding_complete = true). That is deliberate: a
    background sync would silently import every module and destroy the
    no-modules-selected state this script exists to create.

  Free Sandbox → free@local.test    / Test123!Free     (plan: free)
  Pro Sandbox  → premium@local.test / Test123!Premium  (plan: premium)

Idempotent: skips an org if its slug already exists.

Usage:
    venv/bin/python -m scripts.create_test_orgs
    venv/bin/python -m scripts.create_test_orgs --reset          # purge first
    venv/bin/python -m scripts.create_test_orgs --reset --dry-run

--reset DESTRUCTIVELY removes every organization except the internal one
(KEEP_ORG_IDS) and DROPs its tenant schema. Run --dry-run first.
"""

import argparse
import os

from alembic import command as alembic_command
from alembic.config import Config as AlembicConfig
from sqlalchemy import text

from supporthub.app.db import engine, session_scope
from supporthub.app.public_models import Organization, User
from supporthub.app.services.auth_service import hash_password
from supporthub.app.services.tenant_service import _seed_app_settings, make_schema_name

PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
ALEMBIC_INI = os.path.join(PROJECT_ROOT, "alembic.ini")

# Orgs that --reset must never touch: the platform owner (ModuleDesk Internal).
KEEP_ORG_IDS = {1}
# Schemas that must never be dropped no matter what the org table says.
PROTECTED_SCHEMAS = {"public", "tenant_internal", "information_schema"}

ORGS = [
    {"name": "Free Sandbox", "slug": "free-sandbox", "plan": "free",
     "email": "free@local.test", "password": "Test123!Free"},
    {"name": "Pro Sandbox", "slug": "pro-sandbox", "plan": "premium",
     "email": "premium@local.test", "password": "Test123!Premium"},
]


def provision(schema: str) -> None:
    # provision_tenant() imports migrations/env.py directly, which only works
    # under an Alembic context — use the alembic command API instead.
    with engine.connect() as conn:
        conn.execute(text(f"CREATE SCHEMA IF NOT EXISTS {schema}"))
        conn.commit()
    cfg = AlembicConfig(ALEMBIC_INI)
    cfg.attributes["tenant_schema"] = schema
    alembic_command.upgrade(cfg, "head")
    _seed_app_settings(schema)


def encrypted_addons_key() -> bytes | None:
    """Encrypt the platform Addons API key for storage on a sandbox org.

    Uses the same helper the onboarding wizard uses, so the sandbox key is
    byte-compatible with what a real signup would have written.
    """
    from supporthub.app.config import config
    from supporthub.app.onboarding.routes import _encrypt

    if not config.ADDONS_API_KEY:
        print("WARN no ADDONS_API_KEY in the environment — orgs get no key")
        return None
    return _encrypt(config.ADDONS_API_KEY)


def reset(dry_run: bool = False) -> None:
    """Delete every org outside KEEP_ORG_IDS and drop its tenant schema."""
    with session_scope() as session:
        rows = session.execute(text(
            "SELECT id, name, schema_name FROM public.organizations"
            " WHERE id <> ALL(:keep) ORDER BY id"
        ), {"keep": list(KEEP_ORG_IDS)}).fetchall()

        if not rows:
            print("reset: nothing to remove")
            return

        org_ids = [r.id for r in rows]
        schemas = [r.schema_name for r in rows if r.schema_name not in PROTECTED_SCHEMAS]
        user_ids = [r[0] for r in session.execute(text(
            "SELECT id FROM public.users WHERE org_id = ANY(:ids)"), {"ids": org_ids})]

        for r in rows:
            print(f"  - org {r.id:>3} {r.name!r} (schema {r.schema_name})")
        print(f"reset: {len(org_ids)} orgs, {len(user_ids)} users, {len(schemas)} schemas")

        if dry_run:
            print("reset: DRY RUN — nothing deleted")
            return

        # Children first: these FK onto public.users / public.organizations.
        if user_ids:
            for tbl in ("push_subscriptions", "member_schedules"):
                session.execute(text(
                    f"DELETE FROM public.{tbl} WHERE user_id = ANY(:ids)"), {"ids": user_ids})
            session.execute(text(
                "DELETE FROM public.audit_log WHERE user_id = ANY(:ids)"), {"ids": user_ids})
            # invited_by_id may point at a user we are about to delete.
            session.execute(text(
                "UPDATE public.users SET invited_by_id = NULL"
                " WHERE invited_by_id = ANY(:ids)"), {"ids": user_ids})
        session.execute(text(
            "DELETE FROM public.audit_log WHERE org_id = ANY(:ids)"), {"ids": org_ids})
        session.execute(text(
            "DELETE FROM public.users WHERE org_id = ANY(:ids)"), {"ids": org_ids})
        session.execute(text(
            "DELETE FROM public.organizations WHERE id = ANY(:ids)"), {"ids": org_ids})

    # Schema drops run outside the ORM transaction (DDL, autocommit).
    with engine.connect() as conn:
        for schema in schemas:
            assert schema not in PROTECTED_SCHEMAS, f"refusing to drop {schema}"
            conn.execute(text(f'DROP SCHEMA IF EXISTS "{schema}" CASCADE'))
        conn.commit()
    print(f"reset: dropped {len(schemas)} schemas")


def main() -> None:
    ap = argparse.ArgumentParser()
    ap.add_argument("--reset", action="store_true",
                    help="delete all orgs except KEEP_ORG_IDS before creating")
    ap.add_argument("--dry-run", action="store_true",
                    help="with --reset, show what would be removed and stop")
    args = ap.parse_args()

    if args.reset:
        reset(dry_run=args.dry_run)
        if args.dry_run:
            return

    addons_key = encrypted_addons_key()

    for spec in ORGS:
        with session_scope() as session:
            if session.query(Organization).filter_by(slug=spec["slug"]).one_or_none():
                print(f"SKIP {spec['slug']}: org already exists")
                continue
            if session.query(User).filter_by(email=spec["email"]).one_or_none():
                print(f"SKIP {spec['slug']}: user {spec['email']} already exists")
                continue

            org = Organization(
                name=spec["name"],
                slug=spec["slug"],
                schema_name=make_schema_name(spec["slug"]),
                plan=spec["plan"],
                email_verified=True,
                # False on purpose — see the module docstring: it keeps the
                # Celery sync from auto-importing modules.
                onboarding_complete=False,
                trial_ends_at=None,  # no MAX trial — show the real plan UX
                addons_api_key_encrypted=addons_key,
            )
            session.add(org)
            session.flush()

            session.add(User(
                org_id=org.id,
                email=spec["email"],
                password_hash=hash_password(spec["password"]),
                role="admin",
                email_verified=True,
            ))
            session.flush()
            org_id = org.id

        provision(make_schema_name(spec["slug"]))

        print(f"OK   {spec['slug']}: org {org_id}, plan={spec['plan']}, "
              f"addons_key={'yes' if addons_key else 'no'}, modules=0, "
              f"login {spec['email']} / {spec['password']}")


if __name__ == "__main__":
    main()