"""cPanel reseller manager tables

Revision ID: cp1a2b3c4d5e
Revises: m1s2y3n4c5e6
Create Date: 2026-05-03

Creates the six tables backing the WHM/cPanel Reseller Manager module.
Everything is guarded with ``IF NOT EXISTS`` so re-runs are safe.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "cp1a2b3c4d5e"
down_revision = "m1s2y3n4c5e6"
branch_labels = None
depends_on = None


def _has_table(bind, name: str) -> bool:
    inspector = sa.inspect(bind)
    return name in inspector.get_table_names()


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

    if not _has_table(bind, "cpanel_servers"):
        op.create_table(
            "cpanel_servers",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("name", sa.String(120), nullable=False, unique=True),
            sa.Column("hostname", sa.String(255), nullable=False),
            sa.Column("port", sa.Integer, nullable=False, server_default="2087"),
            sa.Column("username", sa.String(120), nullable=False, server_default="root"),
            sa.Column("encrypted_api_token", sa.Text, nullable=False),
            sa.Column("verify_tls", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("notes", sa.Text, nullable=True),
            sa.Column("last_tested_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("last_test_status", sa.String(32), nullable=True),
            sa.Column("last_test_message", sa.Text, nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        )

    if not _has_table(bind, "cpanel_packages"):
        op.create_table(
            "cpanel_packages",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column(
                "server_id",
                sa.Integer,
                sa.ForeignKey("cpanel_servers.id", ondelete="CASCADE"),
                nullable=False,
            ),
            sa.Column("display_name", sa.String(150), nullable=False),
            sa.Column("slug", sa.String(150), nullable=False, unique=True),
            sa.Column("description", sa.Text, nullable=True),
            sa.Column("monthly_price", sa.Numeric(10, 2), nullable=False, server_default="0"),
            sa.Column("currency", sa.String(8), nullable=False, server_default="USD"),
            sa.Column("whmcs_product_id", sa.Integer, nullable=True),
            sa.Column("whm_package_name", sa.String(150), nullable=False),
            sa.Column("disk_quota_mb", sa.Integer, nullable=True),
            sa.Column("bandwidth_mb", sa.Integer, nullable=True),
            sa.Column("max_email_accounts", sa.Integer, nullable=True),
            sa.Column("max_ftp_accounts", sa.Integer, nullable=True),
            sa.Column("max_databases", sa.Integer, nullable=True),
            sa.Column("max_subdomains", sa.Integer, nullable=True),
            sa.Column("max_addon_domains", sa.Integer, nullable=True),
            sa.Column("max_parked_domains", sa.Integer, nullable=True),
            sa.Column("has_ssl", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("has_backup", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("has_ssh", sa.Boolean, nullable=False, server_default=sa.false()),
            sa.Column("has_wordpress_tools", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("has_cloudlinux", sa.Boolean, nullable=False, server_default=sa.false()),
            sa.Column("cpu_limit_percent", sa.Integer, nullable=True),
            sa.Column("memory_limit_mb", sa.Integer, nullable=True),
            sa.Column("io_limit_kbps", sa.Integer, nullable=True),
            sa.Column("iops_limit", sa.Integer, nullable=True),
            sa.Column("ep_limit", sa.Integer, nullable=True),
            sa.Column("nproc_limit", sa.Integer, nullable=True),
            sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.true()),
            sa.Column("sort_order", sa.Integer, nullable=False, server_default="0"),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.UniqueConstraint("server_id", "whm_package_name", name="uq_cpanel_pkg_server_name"),
        )

    if not _has_table(bind, "cpanel_client_accounts"):
        op.create_table(
            "cpanel_client_accounts",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("user_id", sa.String(255), nullable=False, index=True),
            sa.Column(
                "server_id",
                sa.Integer,
                sa.ForeignKey("cpanel_servers.id", ondelete="RESTRICT"),
                nullable=False,
            ),
            sa.Column(
                "package_id",
                sa.Integer,
                sa.ForeignKey("cpanel_packages.id", ondelete="SET NULL"),
                nullable=True,
            ),
            sa.Column("whmcs_service_id", sa.Integer, nullable=True, index=True),
            sa.Column("whmcs_client_id", sa.Integer, nullable=True, index=True),
            sa.Column("cpanel_username", sa.String(120), nullable=False),
            sa.Column("main_domain", sa.String(255), nullable=False),
            sa.Column("contact_email", sa.String(255), nullable=True),
            sa.Column("encrypted_password", sa.Text, nullable=True),
            sa.Column("status", sa.String(32), nullable=False, server_default="pending"),
            sa.Column("suspend_reason", sa.String(255), nullable=True),
            sa.Column("last_error", sa.Text, nullable=True),
            sa.Column("provisioned_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("suspended_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("unsuspended_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("terminated_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.UniqueConstraint("server_id", "cpanel_username", name="uq_cpanel_acct_server_user"),
        )

    if not _has_table(bind, "cpanel_account_usage"):
        op.create_table(
            "cpanel_account_usage",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column(
                "account_id",
                sa.Integer,
                sa.ForeignKey("cpanel_client_accounts.id", ondelete="CASCADE"),
                nullable=False,
                unique=True,
            ),
            sa.Column("disk_used_mb", sa.Integer, nullable=True),
            sa.Column("disk_limit_mb", sa.Integer, nullable=True),
            sa.Column("bandwidth_used_mb", sa.Integer, nullable=True),
            sa.Column("bandwidth_limit_mb", sa.Integer, nullable=True),
            sa.Column("email_accounts", sa.Integer, nullable=True),
            sa.Column("ftp_accounts", sa.Integer, nullable=True),
            sa.Column("databases", sa.Integer, nullable=True),
            sa.Column("subdomains", sa.Integer, nullable=True),
            sa.Column("addon_domains", sa.Integer, nullable=True),
            sa.Column("ssl_status", sa.String(32), nullable=True),
            sa.Column("backup_status", sa.String(32), nullable=True),
            sa.Column("cpu_used_percent", sa.Integer, nullable=True),
            sa.Column("memory_used_mb", sa.Integer, nullable=True),
            sa.Column("entry_processes", sa.Integer, nullable=True),
            sa.Column("last_synced_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        )

    if not _has_table(bind, "cpanel_api_logs"):
        op.create_table(
            "cpanel_api_logs",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("server_id", sa.Integer, sa.ForeignKey("cpanel_servers.id", ondelete="SET NULL"), nullable=True),
            sa.Column(
                "account_id",
                sa.Integer,
                sa.ForeignKey("cpanel_client_accounts.id", ondelete="SET NULL"),
                nullable=True,
            ),
            sa.Column("endpoint", sa.String(255), nullable=False),
            sa.Column("method", sa.String(16), nullable=False, server_default="GET"),
            sa.Column("status", sa.String(32), nullable=False, server_default="ok"),
            sa.Column("http_status", sa.Integer, nullable=True),
            sa.Column("duration_ms", sa.Integer, nullable=True),
            sa.Column("request_preview", sa.Text, nullable=True),
            sa.Column("response_preview", sa.Text, nullable=True),
            sa.Column("error_message", sa.Text, nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, index=True),
        )

    if not _has_table(bind, "cpanel_audit_logs"):
        op.create_table(
            "cpanel_audit_logs",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("action", sa.String(80), nullable=False, index=True),
            sa.Column("admin_user_id", sa.String(255), nullable=True, index=True),
            sa.Column("admin_email", sa.String(255), nullable=True),
            sa.Column(
                "account_id",
                sa.Integer,
                sa.ForeignKey("cpanel_client_accounts.id", ondelete="SET NULL"),
                nullable=True,
            ),
            sa.Column("target_user_id", sa.String(255), nullable=True, index=True),
            sa.Column("details", sa.Text, nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, index=True),
        )


def downgrade() -> None:
    for tbl in (
        "cpanel_audit_logs",
        "cpanel_api_logs",
        "cpanel_account_usage",
        "cpanel_client_accounts",
        "cpanel_packages",
        "cpanel_servers",
    ):
        op.execute(f"DROP TABLE IF EXISTS {tbl} CASCADE")