from typing import Optional
from uuid import UUID

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from src.apps.tenants.models.account import Account
from src.apps.tenants.models.branding_config import BrandingConfig
from src.apps.tenants.models.subscription import Subscription
from src.apps.auth.models.user import User
from src.core.exceptions import ConflictError, NotFoundError, ValidationError
from src.core.security import hash_password
from src.core.constants import AccountStatus, UserRole, UserStatus, SubscriptionPlan, PLAN_PRICES_CAD


class TenantService:
    def __init__(self, db: AsyncSession):
        self.db = db

    async def get_by_slug(self, subdomain: str) -> Optional[Account]:
        result = await self.db.execute(
            select(Account).where(Account.subdomain == subdomain)
        )
        return result.scalar_one_or_none()

    async def get_by_id(self, account_id: UUID) -> Optional[Account]:
        result = await self.db.execute(
            select(Account)
            .options(selectinload(Account.branding))
            .where(Account.id == account_id)
        )
        return result.scalar_one_or_none()

    async def list_all(self, skip: int = 0, limit: int = 50) -> tuple[list, int]:
        from sqlalchemy import func
        count_result = await self.db.execute(select(func.count(Account.id)))
        total = count_result.scalar_one()
        result = await self.db.execute(
            select(Account).order_by(Account.created_at.desc()).offset(skip).limit(limit)
        )
        return result.scalars().all(), total

    async def create(self, data: dict) -> tuple[Account, User]:
        """Provision a new tenant with admin user."""
        # Check subdomain uniqueness
        existing = await self.get_by_slug(data["subdomain"])
        if existing:
            raise ConflictError(f"Subdomain '{data['subdomain']}' is already taken")

        # Create account
        account = Account(
            organization_name=data["organization_name"],
            subdomain=data["subdomain"],
            cemetery_type=data.get("cemetery_type"),
            contact_email=data["contact_email"],
            contact_phone=data.get("contact_phone"),
            address=data.get("address"),
            plan=data.get("plan", SubscriptionPlan.STARTER.value),
            status=AccountStatus.PENDING.value,
        )
        self.db.add(account)
        await self.db.flush()  # get account.id

        # Create branding config
        branding = BrandingConfig(
            account_id=account.id,
            public_site_name=account.organization_name,
        )
        self.db.add(branding)

        # Create subscription record
        plan = data.get("plan", SubscriptionPlan.STARTER.value)
        subscription = Subscription(
            account_id=account.id,
            plan=plan,
            status="active",
            amount_cad=PLAN_PRICES_CAD.get(SubscriptionPlan(plan), 149.00),
            billing_cycle="monthly",
        )
        self.db.add(subscription)

        # Create admin user
        admin_user = User(
            tenant_id=account.id,
            email=data["contact_email"].lower(),
            password_hash=hash_password(data["admin_password"]),
            first_name=data["admin_first_name"],
            last_name=data["admin_last_name"],
            role=UserRole.ADMINISTRATOR.value,
            status=UserStatus.ACTIVE.value,
        )
        self.db.add(admin_user)
        await self.db.flush()

        return account, admin_user

    async def create_from_public_signup(
        self, data: dict, ip_address: Optional[str] = None
    ) -> tuple["Account", "User", str]:
        """Provision a new tenant from the public marketing signup form.

        Derives subdomain from organization_name, generates a secure temporary
        password, splits full name, persists Account + BrandingConfig +
        Subscription + User, writes an audit_log row, and returns the plain-text
        password so the caller can include it in the welcome email.

        The plain-text password is NEVER stored — only the bcrypt hash is.
        """
        from src.core.utils.text import slugify_subdomain, split_full_name, RESERVED_SUBDOMAINS
        from src.core.security import generate_temp_password
        from src.apps.site_admin.models.audit_log import AuditLog

        # ── Email uniqueness check ────────────────────────────────────────────
        email = data["email"].lower()
        email_result = await self.db.execute(
            select(User).where(User.email == email, User.deleted_at.is_(None))
        )
        if email_result.scalar_one_or_none():
            raise ConflictError("An account with this email already exists.")

        # ── Subdomain derivation ──────────────────────────────────────────────
        base_slug = slugify_subdomain(data["organization_name"])

        if base_slug in RESERVED_SUBDOMAINS:
            raise ValidationError(
                f"'{base_slug}' is a reserved subdomain and cannot be used",
                errors=[{"field": "organization_name", "message": "Derived subdomain is reserved"}],
            )

        # Ensure uniqueness; append -2, -3 … on collision
        subdomain = base_slug
        suffix = 2
        while True:
            if not await self.get_by_slug(subdomain):
                break
            subdomain = f"{base_slug}-{suffix}"
            suffix += 1
            if suffix > 100:
                raise ValidationError("Could not generate a unique subdomain for this organization name")

        # ── Credentials ───────────────────────────────────────────────────────
        temp_password = generate_temp_password()
        first_name, last_name = split_full_name(data["name"])

        # ── config_json: optional fields ──────────────────────────────────────
        config_json: dict = {"signup_source": "marketing"}
        if data.get("size"):
            config_json["size"] = data["size"]

        # ── Account ───────────────────────────────────────────────────────────
        plan = data.get("plan") or "professional"
        account = Account(
            organization_name=data["organization_name"],
            subdomain=subdomain,
            cemetery_type=data.get("cemetery_type"),
            contact_email=email,
            contact_phone=data.get("phone"),
            plan=plan,
            status=AccountStatus.PENDING.value,
            config_json=config_json,
        )
        self.db.add(account)
        await self.db.flush()  # obtain account.id

        # ── BrandingConfig ────────────────────────────────────────────────────
        branding = BrandingConfig(
            account_id=account.id,
            public_site_name=account.organization_name,
        )
        self.db.add(branding)

        # ── Subscription ──────────────────────────────────────────────────────
        subscription = Subscription(
            account_id=account.id,
            plan=plan,
            status="active",
            amount_cad=PLAN_PRICES_CAD.get(SubscriptionPlan(plan), 349.00),
            billing_cycle="monthly",
        )
        self.db.add(subscription)

        # ── Admin User ────────────────────────────────────────────────────────
        admin_user = User(
            tenant_id=account.id,
            email=email,
            password_hash=hash_password(temp_password),
            first_name=first_name,
            last_name=last_name,
            role=UserRole.ADMINISTRATOR.value,
            status=UserStatus.ACTIVE.value,
        )
        self.db.add(admin_user)
        await self.db.flush()  # obtain admin_user.id

        # ── Audit Log ─────────────────────────────────────────────────────────
        audit = AuditLog(
            tenant_id=account.id,
            entity_type="account",
            entity_id=account.id,
            action="public_signup",
            new_value={
                "organization_name": account.organization_name,
                "subdomain": subdomain,
                "plan": plan,
            },
            ip_address=ip_address,
        )
        self.db.add(audit)
        await self.db.flush()

        return account, admin_user, temp_password

    async def update(self, account_id: UUID, data: dict) -> Account:
        account = await self.get_by_id(account_id)
        if not account:
            raise NotFoundError("Tenant not found")

        # Auto-set activated_at when transitioning to active for first time
        if data.get("status") == "active" and account.status != "active":
            from datetime import datetime, timezone
            if not account.activated_at:
                account.activated_at = datetime.now(timezone.utc)

        # Extract fields that require updating the admin User, not just the Account
        contact_name = data.pop("contact_name", None)
        contact_email = data.get("contact_email")  # keep in data for Account update too

        if contact_name or contact_email:
            admin_result = await self.db.execute(
                select(User).where(
                    User.tenant_id == account_id,
                    User.role == "administrator",
                    User.deleted_at.is_(None),
                ).order_by(User.created_at.asc()).limit(1)
            )
            admin_user = admin_result.scalar_one_or_none()

            if admin_user and contact_name:
                from src.core.utils.text import split_full_name
                first_name, last_name = split_full_name(contact_name)
                admin_user.first_name = first_name
                admin_user.last_name = last_name

            if admin_user and contact_email:
                new_email = contact_email.lower().strip()
                # Check uniqueness — skip if it's the same email
                if admin_user.email.lower() != new_email:
                    conflict = await self.db.execute(
                        select(User).where(
                            func.lower(User.email) == new_email,
                            User.deleted_at.is_(None),
                        )
                    )
                    if conflict.scalar_one_or_none():
                        raise ConflictError("A user with this email already exists.")
                    admin_user.email = new_email

        for field, value in data.items():
            if value is not None and hasattr(account, field):
                setattr(account, field, value)

        await self.db.flush()
        return account

    async def list_signups(
        self,
        skip: int = 0,
        limit: int = 20,
        status: Optional[str] = None,
        plan: Optional[str] = None,
        q: Optional[str] = None,
    ) -> tuple[list, int]:
        """Paginated list of sign-up accounts joining first administrator user for contact info."""
        from sqlalchemy import func, or_, and_

        # Build base conditions
        conditions = []
        if status:
            conditions.append(Account.status == status)
        if plan:
            conditions.append(Account.plan == plan.lower())

        # Join the first administrator user per account to get contact name/email
        admin_user_subq = (
            select(
                User.tenant_id,
                User.first_name,
                User.last_name,
                User.email.label("user_email"),
            )
            .where(
                User.role == "administrator",
                User.deleted_at.is_(None),
            )
            .distinct(User.tenant_id)
            .order_by(User.tenant_id, User.created_at.asc())
            .subquery()
        )

        stmt = (
            select(Account, admin_user_subq)
            .outerjoin(admin_user_subq, Account.id == admin_user_subq.c.tenant_id)
        )

        if conditions:
            stmt = stmt.where(and_(*conditions))

        if q:
            q_lower = f"%{q.lower()}%"
            contact_name_col = func.lower(
                func.concat(admin_user_subq.c.first_name, " ", admin_user_subq.c.last_name)
            )
            stmt = stmt.where(
                or_(
                    func.lower(Account.organization_name).like(q_lower),
                    func.lower(admin_user_subq.c.user_email).like(q_lower),
                    contact_name_col.like(q_lower),
                )
            )

        count_stmt = select(func.count()).select_from(stmt.subquery())
        total_result = await self.db.execute(count_stmt)
        total = total_result.scalar_one()

        stmt = stmt.order_by(Account.created_at.desc()).offset(skip).limit(limit)
        result = await self.db.execute(stmt)
        rows = result.all()

        items = []
        for row in rows:
            account = row[0]
            # Subquery columns: [1]=tenant_id, [2]=first_name, [3]=last_name, [4]=user_email
            first_name = row[2]
            last_name = row[3]
            user_email_from_user = row[4]

            contact_name_str = f"{first_name or ''} {last_name or ''}".strip()
            user_email = user_email_from_user or account.contact_email

            items.append({
                "account": account,
                "contact_name": contact_name_str,
                "contact_email": user_email,
                "contact_phone": account.contact_phone,
            })

        return items, total

    async def get_signup_kpis(self) -> dict:
        """Count accounts by status for KPI strip cards."""
        from sqlalchemy import func

        result = await self.db.execute(
            select(Account.status, func.count(Account.id).label("cnt"))
            .group_by(Account.status)
        )
        rows = result.all()
        counts = {row[0]: row[1] for row in rows}
        total = sum(counts.values())
        return {
            "new": counts.get("pending", 0) + counts.get("new", 0),
            "onboarding": counts.get("onboarding", 0),
            "active": counts.get("active", 0),
            "total": total,
        }

    async def admin_create(self, data: dict) -> tuple[Account, User, str]:
        """Create account on behalf of a customer (site-admin wizard).

        Generates a random subdomain from organization_name, creates
        Account + BrandingConfig + Subscription + User (administrator role),
        returns (account, user, temp_password).
        """
        from src.core.utils.text import slugify_subdomain, split_full_name, RESERVED_SUBDOMAINS
        from src.core.security import generate_temp_password

        email = data["contact_email"].lower()
        email_result = await self.db.execute(
            select(User).where(User.email == email, User.deleted_at.is_(None))
        )
        if email_result.scalar_one_or_none():
            raise ConflictError("An account with this email already exists.")

        base_slug = slugify_subdomain(data["organization_name"])
        if base_slug in RESERVED_SUBDOMAINS:
            raise ValidationError(
                f"'{base_slug}' is a reserved subdomain",
                errors=[{"field": "organization_name", "message": "Derived subdomain is reserved"}],
            )

        subdomain = base_slug
        suffix = 2
        while True:
            if not await self.get_by_slug(subdomain):
                break
            subdomain = f"{base_slug}-{suffix}"
            suffix += 1
            if suffix > 100:
                raise ValidationError("Could not generate a unique subdomain")

        temp_password = generate_temp_password()
        contact_name = data.get("contact_name", "")
        first_name, last_name = split_full_name(contact_name)

        plan = data.get("plan") or "professional"
        account = Account(
            organization_name=data["organization_name"],
            subdomain=subdomain,
            cemetery_type=data.get("cemetery_type"),
            contact_email=email,
            contact_phone=data.get("contact_phone"),
            plan=plan,
            status=AccountStatus.PENDING.value,
            signup_source=data.get("signup_source", "admin"),
            size=data.get("size"),
        )
        self.db.add(account)
        await self.db.flush()

        branding = BrandingConfig(
            account_id=account.id,
            public_site_name=account.organization_name,
        )
        self.db.add(branding)

        subscription = Subscription(
            account_id=account.id,
            plan=plan,
            status="active",
            amount_cad=PLAN_PRICES_CAD.get(SubscriptionPlan(plan), 349.00),
            billing_cycle="monthly",
        )
        self.db.add(subscription)

        admin_user = User(
            tenant_id=account.id,
            email=email,
            password_hash=hash_password(temp_password),
            first_name=first_name,
            last_name=last_name,
            role=UserRole.ADMINISTRATOR.value,
            status=UserStatus.ACTIVE.value,
        )
        self.db.add(admin_user)
        await self.db.flush()

        return account, admin_user, temp_password

    async def send_credentials(self, account_id: UUID, sent_at: str) -> Account:
        """Record credentials_sent_at timestamp on the account."""
        from datetime import datetime, timezone

        account = await self.get_by_id(account_id)
        if not account:
            raise NotFoundError("Tenant not found")

        try:
            dt = datetime.fromisoformat(sent_at.replace("Z", "+00:00"))
        except (ValueError, AttributeError):
            dt = datetime.now(timezone.utc)

        account.credentials_sent_at = dt
        await self.db.flush()
        return account

    async def set_feature_flags(self, account_id: UUID, flags: dict) -> Account:
        """Merge feature flag overrides into account.feature_flags."""
        account = await self.get_by_id(account_id)
        if not account:
            raise NotFoundError("Tenant not found")

        existing = account.feature_flags or {}
        existing.update(flags)
        account.feature_flags = existing
        await self.db.flush()
        return account

    async def update_branding(self, account_id: UUID, data: dict) -> BrandingConfig:
        result = await self.db.execute(
            select(BrandingConfig).where(BrandingConfig.account_id == account_id)
        )
        branding = result.scalar_one_or_none()
        if not branding:
            branding = BrandingConfig(account_id=account_id)
            self.db.add(branding)

        for field, value in data.items():
            if value is not None and hasattr(branding, field):
                setattr(branding, field, value)

        await self.db.flush()
        return branding