"""bizmail360 reseller manager tables

Revision ID: b1z3m6a7i9l0
Revises: ff1a2b3c4d5e
Create Date: 2026-05-03

Creates the four tables backing the Bizmail360 Reseller Manager:
 - bizmail_plans
 - bizmail_subscriptions
 - bizmail_mailboxes
 - bizmail_audit_log

Idempotent: each CREATE TABLE / CREATE INDEX is guarded with
``IF NOT EXISTS`` so repeated bootstraps never fail.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "b1z3m6a7i9l0"
down_revision = "ff1a2b3c4d5e"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    dialect = bind.dialect.name

    # --- bizmail_plans ---
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS bizmail_plans (
            id SERIAL PRIMARY KEY,
            slug VARCHAR(64) NOT NULL,
            name VARCHAR(128) NOT NULL,
            description TEXT,
            price_monthly VARCHAR(32),
            price_yearly VARCHAR(32),
            currency VARCHAR(8) NOT NULL DEFAULT 'USD',
            mailbox_quota INTEGER NOT NULL DEFAULT 5,
            storage_mb_per_mailbox INTEGER NOT NULL DEFAULT 1024,
            aliases_included INTEGER NOT NULL DEFAULT 20,
            features TEXT,
            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            is_featured BOOLEAN NOT NULL DEFAULT FALSE,
            sort_order INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
        )
        """
        if dialect == "postgresql"
        else """
        CREATE TABLE IF NOT EXISTS bizmail_plans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug VARCHAR(64) NOT NULL,
            name VARCHAR(128) NOT NULL,
            description TEXT,
            price_monthly VARCHAR(32),
            price_yearly VARCHAR(32),
            currency VARCHAR(8) NOT NULL DEFAULT 'USD',
            mailbox_quota INTEGER NOT NULL DEFAULT 5,
            storage_mb_per_mailbox INTEGER NOT NULL DEFAULT 1024,
            aliases_included INTEGER NOT NULL DEFAULT 20,
            features TEXT,
            is_active BOOLEAN NOT NULL DEFAULT 1,
            is_featured BOOLEAN NOT NULL DEFAULT 0,
            sort_order INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    op.execute("CREATE UNIQUE INDEX IF NOT EXISTS uq_bizmail_plans_slug ON bizmail_plans (slug)")

    # --- bizmail_subscriptions ---
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS bizmail_subscriptions (
            id SERIAL PRIMARY KEY,
            user_id VARCHAR(255) NOT NULL,
            user_email VARCHAR(255),
            plan_id INTEGER NOT NULL,
            domain_name VARCHAR(255) NOT NULL,
            smartermail_domain_ref VARCHAR(255),
            status VARCHAR(16) NOT NULL DEFAULT 'pending',
            billing_cycle VARCHAR(16) NOT NULL DEFAULT 'monthly',
            primary_mailbox VARCHAR(255),
            provisioned_at TIMESTAMP WITH TIME ZONE,
            activated_at TIMESTAMP WITH TIME ZONE,
            suspended_at TIMESTAMP WITH TIME ZONE,
            cancelled_at TIMESTAMP WITH TIME ZONE,
            current_period_end TIMESTAMP WITH TIME ZONE,
            notes TEXT,
            mailboxes_used INTEGER NOT NULL DEFAULT 0,
            aliases_used INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
        )
        """
        if dialect == "postgresql"
        else """
        CREATE TABLE IF NOT EXISTS bizmail_subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id VARCHAR(255) NOT NULL,
            user_email VARCHAR(255),
            plan_id INTEGER NOT NULL,
            domain_name VARCHAR(255) NOT NULL,
            smartermail_domain_ref VARCHAR(255),
            status VARCHAR(16) NOT NULL DEFAULT 'pending',
            billing_cycle VARCHAR(16) NOT NULL DEFAULT 'monthly',
            primary_mailbox VARCHAR(255),
            provisioned_at TIMESTAMP,
            activated_at TIMESTAMP,
            suspended_at TIMESTAMP,
            cancelled_at TIMESTAMP,
            current_period_end TIMESTAMP,
            notes TEXT,
            mailboxes_used INTEGER NOT NULL DEFAULT 0,
            aliases_used INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    op.execute("CREATE INDEX IF NOT EXISTS ix_bizmail_subs_user_id ON bizmail_subscriptions (user_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_bizmail_subs_status ON bizmail_subscriptions (status)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_bizmail_subs_domain ON bizmail_subscriptions (domain_name)")

    # --- bizmail_mailboxes ---
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS bizmail_mailboxes (
            id SERIAL PRIMARY KEY,
            subscription_id INTEGER NOT NULL,
            user_id VARCHAR(255) NOT NULL,
            username VARCHAR(255) NOT NULL,
            display_name VARCHAR(255),
            is_domain_admin BOOLEAN NOT NULL DEFAULT FALSE,
            disabled BOOLEAN NOT NULL DEFAULT FALSE,
            storage_mb INTEGER,
            last_synced_at TIMESTAMP WITH TIME ZONE,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
        )
        """
        if dialect == "postgresql"
        else """
        CREATE TABLE IF NOT EXISTS bizmail_mailboxes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            subscription_id INTEGER NOT NULL,
            user_id VARCHAR(255) NOT NULL,
            username VARCHAR(255) NOT NULL,
            display_name VARCHAR(255),
            is_domain_admin BOOLEAN NOT NULL DEFAULT 0,
            disabled BOOLEAN NOT NULL DEFAULT 0,
            storage_mb INTEGER,
            last_synced_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    op.execute(
        "CREATE UNIQUE INDEX IF NOT EXISTS uq_bizmail_mbox_sub_user ON bizmail_mailboxes (subscription_id, username)"
    )
    op.execute("CREATE INDEX IF NOT EXISTS ix_bizmail_mbox_sub ON bizmail_mailboxes (subscription_id)")

    # --- bizmail_audit_log ---
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS bizmail_audit_log (
            id SERIAL PRIMARY KEY,
            subscription_id INTEGER,
            actor_user_id VARCHAR(255),
            actor_email VARCHAR(255),
            action VARCHAR(64) NOT NULL,
            target VARCHAR(255),
            status VARCHAR(16) NOT NULL DEFAULT 'ok',
            payload TEXT,
            error_message TEXT,
            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
        )
        """
        if dialect == "postgresql"
        else """
        CREATE TABLE IF NOT EXISTS bizmail_audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            subscription_id INTEGER,
            actor_user_id VARCHAR(255),
            actor_email VARCHAR(255),
            action VARCHAR(64) NOT NULL,
            target VARCHAR(255),
            status VARCHAR(16) NOT NULL DEFAULT 'ok',
            payload TEXT,
            error_message TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_bizmail_audit_subscription ON bizmail_audit_log (subscription_id)"
    )
    op.execute("CREATE INDEX IF NOT EXISTS ix_bizmail_audit_created ON bizmail_audit_log (created_at)")


def downgrade() -> None:
    # Keep downgrade simple — the module tables are not shared.
    op.execute("DROP TABLE IF EXISTS bizmail_audit_log")
    op.execute("DROP TABLE IF EXISTS bizmail_mailboxes")
    op.execute("DROP TABLE IF EXISTS bizmail_subscriptions")
    op.execute("DROP TABLE IF EXISTS bizmail_plans")