#!/usr/bin/env bash
#
# HostGete Panel — 1-Click Unattended Installer
#
#   curl -fsSL https://get.hostgete.io/install.sh | sudo bash
#
# Performs: root/OS verification, dependency installation (web server,
# database, mail, ftp, TLS), PostgreSQL provisioning, schema ingestion,
# and systemd bootstrap for the hostgete-cored daemon.
#
# Supported OS: Ubuntu 24.04 / 22.04 LTS, AlmaLinux 9 / 8,
#               CloudLinux 9 / 8, Rocky Linux 9 / 8.
#
# Log: /var/log/hostgete-install.log
#
set -euo pipefail
IFS=$'\n\t'

# =========================================================================
# GLOBAL CONSTANTS
# =========================================================================

readonly HOSTGETE_VERSION="1.0.0"
readonly INSTALL_ROOT="/usr/local/hostgete"
readonly BIN_DIR="${INSTALL_ROOT}/bin"
readonly CONF_DIR="${INSTALL_ROOT}/conf"
readonly LOG_DIR="${INSTALL_ROOT}/logs"
readonly SCRIPTS_DIR="${INSTALL_ROOT}/scripts"
readonly MIGRATIONS_DIR="${INSTALL_ROOT}/migrations"
readonly STATE_DIR="${INSTALL_ROOT}/state"

readonly INSTALL_LOG="/var/log/hostgete-install.log"
readonly ENV_FILE="${CONF_DIR}/hostgete.env"
readonly SCHEMA_FILE="${MIGRATIONS_DIR}/001_init_schema.sql"
readonly SYSTEMD_UNIT="/etc/systemd/system/hostgete-cored.service"

readonly DB_NAME="hostgete_db"
readonly DB_USER="hostgete_admin"
readonly DB_HOST="127.0.0.1"
readonly DB_PORT="5432"

readonly PANEL_ADMIN_USER="admin"

SUPPORTED_OS=0
OS_ID=""
OS_VERSION_ID=""
OS_FAMILY=""     # "debian" or "rhel"
PG_MAJOR="16"

# =========================================================================
# LOGGING & ERROR HANDLING
# =========================================================================

_ts() { date '+%Y-%m-%d %H:%M:%S'; }

log()   { echo "[$(_ts)] [INFO]  $*" | tee -a "${INSTALL_LOG}"; }
warn()  { echo "[$(_ts)] [WARN]  $*" | tee -a "${INSTALL_LOG}" >&2; }
fail()  { echo "[$(_ts)] [ERROR] $*" | tee -a "${INSTALL_LOG}" >&2; exit 1; }

on_error() {
    local exit_code=$?
    local line_no=$1
    fail "Installer aborted (exit ${exit_code}) at line ${line_no}. Full log: ${INSTALL_LOG}"
}
trap 'on_error ${LINENO}' ERR

# =========================================================================
# STEP 0 — ROOT & OS VERIFICATION
# =========================================================================

require_root() {
    if [[ "${EUID}" -ne 0 ]]; then
        fail "This installer must be run as root (or via sudo)."
    fi
    log "Root privileges confirmed."
}

detect_os() {
    if [[ ! -f /etc/os-release ]]; then
        fail "Cannot detect operating system: /etc/os-release not found."
    fi

    # shellcheck disable=SC1091
    source /etc/os-release
    OS_ID="${ID:-unknown}"
    OS_VERSION_ID="${VERSION_ID:-0}"

    log "Detected OS: ${PRETTY_NAME:-${OS_ID} ${OS_VERSION_ID}}"

    case "${OS_ID}" in
        ubuntu)
            OS_FAMILY="debian"
            case "${OS_VERSION_ID}" in
                24.04|22.04) SUPPORTED_OS=1 ;;
                *) SUPPORTED_OS=0 ;;
            esac
            ;;
        almalinux|rocky)
            OS_FAMILY="rhel"
            case "${OS_VERSION_ID%%.*}" in
                9|8) SUPPORTED_OS=1 ;;
                *) SUPPORTED_OS=0 ;;
            esac
            ;;
        cloudlinux)
            OS_FAMILY="rhel"
            case "${OS_VERSION_ID%%.*}" in
                9|8) SUPPORTED_OS=1 ;;
                *) SUPPORTED_OS=0 ;;
            esac
            ;;
        *)
            OS_FAMILY="unknown"
            SUPPORTED_OS=0
            ;;
    esac

    if [[ "${SUPPORTED_OS}" -ne 1 ]]; then
        fail "Unsupported or obsolete OS: ${OS_ID} ${OS_VERSION_ID}. Supported: Ubuntu 24.04/22.04 LTS, AlmaLinux 9/8, CloudLinux 9/8, Rocky Linux 9/8."
    fi

    log "OS check passed: ${OS_ID} ${OS_VERSION_ID} (family: ${OS_FAMILY})."
}

check_minimum_resources() {
    local mem_kb
    mem_kb=$(awk '/MemTotal/ {print $2}' /proc/meminfo)
    local mem_mb=$(( mem_kb / 1024 ))

    if (( mem_mb < 2048 )); then
        fail "Insufficient RAM: ${mem_mb}MB detected, minimum 2048MB (2GB) required."
    fi

    local free_root_mb
    free_root_mb=$(df --output=avail -m / | tail -1 | tr -d ' ')
    if (( free_root_mb < 10240 )); then
        fail "Insufficient disk space on /: ${free_root_mb}MB free, minimum 10240MB (10GB) required."
    fi

    log "Resource check passed (${mem_mb}MB RAM, ${free_root_mb}MB free on /)."
}

check_required_ports() {
    log "Checking that ports 80 (HTTP/ACME) and 53 (DNS) are free..."

    if ! command -v ss >/dev/null 2>&1; then
        warn "'ss' not found — skipping port availability check. Verify ports 80 and 53 manually."
        return
    fi

    local port
    for port in 80 53; do
        if ss -H -tuln "( sport = :${port} )" 2>/dev/null | grep -q .; then
            local occupant
            occupant=$(ss -H -tulpn "( sport = :${port} )" 2>/dev/null | awk '{print $NF}' | head -1)
            fail "Port ${port} is already in use (${occupant:-unknown process}). HostGete Panel requires ports 80 and 53 to be free before install. Stop the conflicting service and re-run."
        fi
    done

    log "Ports 80 and 53 are clean."
}

# =========================================================================
# STEP 1 — AUTOMATED DEPENDENCY LAYER
# =========================================================================

install_deps_debian() {
    log "Configuring apt repositories (Debian/Ubuntu family)..."
    export DEBIAN_FRONTEND=noninteractive

    apt-get update -y >>"${INSTALL_LOG}" 2>&1
    apt-get install -y --no-install-recommends \
        curl wget gnupg2 ca-certificates lsb-release apt-transport-https \
        software-properties-common openssl uuid-runtime >>"${INSTALL_LOG}" 2>&1

    # --- PostgreSQL 16+ (PGDG official repo) ---
    install -d /usr/share/postgresql-common/pgdg
    curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
        -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
    echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
        > /etc/apt/sources.list.d/pgdg.list

    # --- LiteSpeed / OpenLiteSpeed repo ---
    curl -fsSL https://repo.litespeed.sh | bash >>"${INSTALL_LOG}" 2>&1 || \
        warn "LiteSpeed repo bootstrap script returned non-zero; continuing with OpenLiteSpeed only."

    apt-get update -y >>"${INSTALL_LOG}" 2>&1

    log "Installing PostgreSQL ${PG_MAJOR}, OpenLiteSpeed, Pure-FTPd, Exim, Dovecot, Certbot..."
    apt-get install -y \
        "postgresql-${PG_MAJOR}" "postgresql-client-${PG_MAJOR}" \
        openlitespeed \
        pure-ftpd \
        exim4 exim4-daemon-heavy \
        dovecot-core dovecot-imapd dovecot-pop3d dovecot-lmtpd \
        certbot python3-certbot-nginx \
        clamav clamav-daemon \
        rclone jq \
        >>"${INSTALL_LOG}" 2>&1

    log "Debian/Ubuntu dependency layer installed."
}

install_deps_rhel() {
    log "Configuring dnf repositories (RHEL family: AlmaLinux/CloudLinux/Rocky)..."

    dnf install -y epel-release >>"${INSTALL_LOG}" 2>&1 || true
    dnf install -y dnf-plugins-core curl wget openssl util-linux \
        >>"${INSTALL_LOG}" 2>&1

    # --- PostgreSQL 16+ (PGDG official repo) ---
    local rhel_major="${OS_VERSION_ID%%.*}"
    dnf install -y "https://download.postgresql.org/pub/repos/yum/reporpms/EL-${rhel_major}-x86_64/pgdg-redhat-repo-latest.noarch.rpm" \
        >>"${INSTALL_LOG}" 2>&1
    dnf -qy module disable postgresql >>"${INSTALL_LOG}" 2>&1 || true

    # --- LiteSpeed / OpenLiteSpeed repo ---
    curl -fsSL https://repo.litespeed.sh | bash >>"${INSTALL_LOG}" 2>&1 || \
        warn "LiteSpeed repo bootstrap script returned non-zero; continuing with OpenLiteSpeed only."

    log "Installing PostgreSQL ${PG_MAJOR}, OpenLiteSpeed, Pure-FTPd, Exim, Dovecot, Certbot..."
    dnf install -y \
        "postgresql${PG_MAJOR}-server" "postgresql${PG_MAJOR}" \
        openlitespeed \
        pure-ftpd \
        exim \
        dovecot \
        certbot python3-certbot-nginx \
        clamav clamav-update \
        rclone jq \
        >>"${INSTALL_LOG}" 2>&1

    "/usr/pgsql-${PG_MAJOR}/bin/postgresql-${PG_MAJOR}-setup" --initdb \
        >>"${INSTALL_LOG}" 2>&1 || true

    log "RHEL-family dependency layer installed."
}

install_dependencies() {
    case "${OS_FAMILY}" in
        debian) install_deps_debian ;;
        rhel)   install_deps_rhel ;;
        *)      fail "Unknown OS family — cannot select a dependency installation path." ;;
    esac
}

# =========================================================================
# STEP 2 — AUTONOMOUS DATABASE PROVISIONING
# =========================================================================

generate_secret() {
    local length="${1:-32}"
    # Alphanumeric only, so the secret is always safe to embed unescaped
    # in shell heredocs, systemd EnvironmentFile lines, and SQL literals.
    openssl rand -base64 48 | tr -dc 'A-Za-z0-9' | head -c "${length}"
}

start_postgresql() {
    log "Starting and enabling PostgreSQL..."

    local pg_service
    if [[ "${OS_FAMILY}" == "debian" ]]; then
        pg_service="postgresql"
    else
        pg_service="postgresql-${PG_MAJOR}"
    fi

    systemctl enable --now "${pg_service}" >>"${INSTALL_LOG}" 2>&1

    local retries=0
    until su - postgres -c "pg_isready -q" >>"${INSTALL_LOG}" 2>&1; do
        retries=$((retries + 1))
        if (( retries > 30 )); then
            fail "PostgreSQL did not become ready after 30 attempts."
        fi
        sleep 2
    done

    log "PostgreSQL is running and accepting connections."
}

provision_database() {
    log "Provisioning HostGete Panel database and role..."

    DB_PASSWORD="$(generate_secret 32)"
    readonly DB_PASSWORD

    # Idempotent: skip creation if the role/database already exist
    # (re-running the installer should not fail or rotate credentials).
    local role_exists
    role_exists=$(su - postgres -c "psql -tAc \"SELECT 1 FROM pg_roles WHERE rolname='${DB_USER}'\"")

    if [[ "${role_exists}" == "1" ]]; then
        warn "Role '${DB_USER}' already exists — leaving existing credentials untouched."
        DB_PASSWORD="(unchanged — see ${ENV_FILE})"
    else
        su - postgres -c "psql -v ON_ERROR_STOP=1" <<-SQL >>"${INSTALL_LOG}" 2>&1
			CREATE ROLE ${DB_USER} WITH LOGIN PASSWORD '${DB_PASSWORD}';
			ALTER ROLE ${DB_USER} SET client_encoding TO 'utf8';
			ALTER ROLE ${DB_USER} SET default_transaction_isolation TO 'read committed';
		SQL
        log "Created database role '${DB_USER}'."
    fi

    local db_exists
    db_exists=$(su - postgres -c "psql -tAc \"SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'\"")

    if [[ "${db_exists}" == "1" ]]; then
        warn "Database '${DB_NAME}' already exists — skipping CREATE DATABASE."
    else
        su - postgres -c "psql -v ON_ERROR_STOP=1" <<-SQL >>"${INSTALL_LOG}" 2>&1
			CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
			GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};
		SQL
        log "Created database '${DB_NAME}' owned by '${DB_USER}'."
    fi
}

# =========================================================================
# STEP 3 — SCHEMA AUTO-INGESTION
# =========================================================================

write_schema_file() {
    log "Writing schema to ${SCHEMA_FILE}..."
    install -d -m 750 "${MIGRATIONS_DIR}"

    cat > "${SCHEMA_FILE}" <<'HOSTGETE_SCHEMA_EOF'
-- =====================================================================
-- HostGete Panel — Core Relational Schema (PostgreSQL 16+)
-- Phase 1: Foundational multi-tenant hosting control panel schema.
-- Auto-ingested by install.sh — do not edit in place; edit the source
-- migration in backend/migrations/ and re-run the installer/updater.
-- =====================================================================


-- ---------------------------------------------------------------------
-- Extensions
-- ---------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS "pgcrypto";     -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "citext";       -- case-insensitive emails/usernames

-- ---------------------------------------------------------------------
-- ENUM TYPES
-- ---------------------------------------------------------------------
CREATE TYPE account_role       AS ENUM ('admin', 'reseller', 'user');
CREATE TYPE account_status     AS ENUM ('active', 'suspended', 'terminated', 'pending');
CREATE TYPE overselling_mode   AS ENUM ('strict', 'oversell');
CREATE TYPE domain_type        AS ENUM ('primary', 'addon', 'subdomain', 'parked');
CREATE TYPE webserver_engine   AS ENUM ('litespeed_enterprise', 'openlitespeed');
CREATE TYPE db_engine          AS ENUM ('mysql', 'postgresql');
CREATE TYPE mail_record_type   AS ENUM ('mailbox', 'forwarder', 'alias');
CREATE TYPE dns_record_type    AS ENUM ('A', 'AAAA', 'CNAME', 'MX', 'TXT', 'SRV', 'NS');
CREATE TYPE backup_destination AS ENUM ('s3', 'google_drive', 'wasabi', 'ftp', 'local');
CREATE TYPE backup_status      AS ENUM ('queued', 'running', 'completed', 'failed');
CREATE TYPE ticket_severity    AS ENUM ('info', 'warning', 'critical');
CREATE TYPE app_runtime        AS ENUM ('nodejs', 'python_django', 'python_fastapi', 'ruby');
CREATE TYPE update_run_status  AS ENUM ('pending', 'running', 'success', 'failed', 'rolled_back');

-- ---------------------------------------------------------------------
-- 1. IDENTITY, ROLES & MULTI-TENANT HIERARCHY
-- ---------------------------------------------------------------------

-- Every account (admin, reseller, or end-user) lives in one table with a
-- self-referencing owner_id so resellers "own" their end-users, and the
-- WHM-equivalent admin owns top-level resellers (owner_id NULL).
CREATE TABLE accounts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id            UUID REFERENCES accounts(id) ON DELETE RESTRICT,
    role                account_role NOT NULL,
    username            CITEXT NOT NULL UNIQUE,
    email               CITEXT NOT NULL UNIQUE,
    password_hash       TEXT NOT NULL,
    status              account_status NOT NULL DEFAULT 'pending',
    two_factor_secret   TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    suspended_at        TIMESTAMPTZ,
    suspension_reason   TEXT,
    CONSTRAINT chk_admin_has_no_owner
        CHECK (role <> 'admin' OR owner_id IS NULL)
);
CREATE INDEX idx_accounts_owner_id ON accounts(owner_id);
CREATE INDEX idx_accounts_role     ON accounts(role);
CREATE INDEX idx_accounts_status   ON accounts(status);

-- API bearer tokens issued to accounts (panel SPA, WHMCS module, AI agents)
CREATE TABLE api_tokens (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id      UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    token_hash      TEXT NOT NULL UNIQUE,
    label           TEXT,
    scopes          TEXT[] NOT NULL DEFAULT '{}',
    last_used_at    TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at      TIMESTAMPTZ
);
CREATE INDEX idx_api_tokens_account_id ON api_tokens(account_id);

-- Reseller-specific white-label configuration
CREATE TABLE reseller_profiles (
    account_id          UUID PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
    company_name        TEXT,
    custom_logo_url     TEXT,
    vanity_ns1          TEXT,
    vanity_ns2          TEXT,
    overselling_mode    overselling_mode NOT NULL DEFAULT 'strict',
    max_accounts        INTEGER NOT NULL DEFAULT 0,
    max_disk_mb         BIGINT NOT NULL DEFAULT 0,
    max_bandwidth_mb    BIGINT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------
-- 2. PACKAGES & RESOURCE ALLOCATION (CloudLinux LVE + quotas)
-- ---------------------------------------------------------------------

CREATE TABLE packages (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id            UUID REFERENCES accounts(id) ON DELETE CASCADE, -- NULL = global admin package
    name                TEXT NOT NULL,
    disk_quota_mb       BIGINT NOT NULL,
    bandwidth_quota_mb  BIGINT NOT NULL,
    max_domains         INTEGER NOT NULL DEFAULT 1,
    max_subdomains      INTEGER NOT NULL DEFAULT 10,
    max_databases       INTEGER NOT NULL DEFAULT 5,
    max_mailboxes       INTEGER NOT NULL DEFAULT 10,
    max_ftp_accounts    INTEGER NOT NULL DEFAULT 5,
    -- CloudLinux LVE hardware throttling defaults (lvectl)
    lve_cpu_percent     SMALLINT NOT NULL DEFAULT 100,
    lve_mem_mb          INTEGER  NOT NULL DEFAULT 1024,
    lve_io_mbps         INTEGER  NOT NULL DEFAULT 20,
    lve_entry_procs     INTEGER  NOT NULL DEFAULT 20,
    is_metered_billing  BOOLEAN NOT NULL DEFAULT false,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (owner_id, name)
);
CREATE INDEX idx_packages_owner_id ON packages(owner_id);

-- Assignment of a package to a specific end-user account (snapshot of
-- limits at assignment time, since packages can change later).
CREATE TABLE account_packages (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL UNIQUE REFERENCES accounts(id) ON DELETE CASCADE,
    package_id          UUID NOT NULL REFERENCES packages(id) ON DELETE RESTRICT,
    disk_quota_mb       BIGINT NOT NULL,
    bandwidth_quota_mb  BIGINT NOT NULL,
    lve_cpu_percent     SMALLINT NOT NULL,
    lve_mem_mb          INTEGER NOT NULL,
    lve_io_mbps         INTEGER NOT NULL,
    lve_entry_procs     INTEGER NOT NULL,
    assigned_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- OS-level disk quota enforcement ledger (setquota/xfs_quota mirror)
CREATE TABLE disk_quota_states (
    account_id          UUID PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
    filesystem_path     TEXT NOT NULL,
    soft_limit_mb       BIGINT NOT NULL,
    hard_limit_mb       BIGINT NOT NULL,
    used_mb             BIGINT NOT NULL DEFAULT 0,
    last_synced_at      TIMESTAMPTZ
);

-- ---------------------------------------------------------------------
-- 3. DOMAINS, VIRTUAL HOSTS & DNS
-- ---------------------------------------------------------------------

CREATE TABLE domains (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    parent_domain_id    UUID REFERENCES domains(id) ON DELETE CASCADE, -- for subdomains/addons
    fqdn                CITEXT NOT NULL UNIQUE,
    type                domain_type NOT NULL DEFAULT 'primary',
    document_root       TEXT NOT NULL,
    php_version         TEXT NOT NULL DEFAULT '8.3',
    ssl_enabled         BOOLEAN NOT NULL DEFAULT false,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_domains_account_id ON domains(account_id);
CREATE INDEX idx_domains_parent_id  ON domains(parent_domain_id);

-- NOTE: vhosts intentionally holds no reference to ssl_certificates.
-- The active cert for a domain is looked up via ssl_certificates.current_active,
-- which avoids the circular vhosts <-> ssl_certificates dependency entirely.
CREATE TABLE vhosts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL UNIQUE REFERENCES domains(id) ON DELETE CASCADE,
    webserver_engine    webserver_engine NOT NULL DEFAULT 'openlitespeed',
    config_path         TEXT NOT NULL,
    htaccess_path       TEXT,
    rewrite_rules       TEXT,
    listen_port         INTEGER NOT NULL DEFAULT 80,
    last_deployed_at    TIMESTAMPTZ,
    last_restart_status TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ssl_certificates (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    issuer              TEXT NOT NULL DEFAULT 'lets_encrypt',
    cert_path           TEXT NOT NULL,
    key_path            TEXT NOT NULL,
    chain_path          TEXT,
    issued_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at          TIMESTAMPTZ NOT NULL,
    auto_renew          BOOLEAN NOT NULL DEFAULT true,
    current_active      BOOLEAN NOT NULL DEFAULT false -- the cert currently deployed/serving this domain's vhost
);
CREATE INDEX idx_ssl_certificates_domain_id ON ssl_certificates(domain_id);
CREATE INDEX idx_ssl_certificates_expires    ON ssl_certificates(expires_at);

-- Enforces at most one active cert per domain (partial unique index instead
-- of a boolean FK) — this is what replaces vhosts.ssl_cert_id.
CREATE UNIQUE INDEX idx_ssl_certificates_one_active_per_domain
    ON ssl_certificates(domain_id)
    WHERE current_active = true;

CREATE TABLE dns_zones (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL UNIQUE REFERENCES domains(id) ON DELETE CASCADE,
    zone_file_path      TEXT NOT NULL,
    serial              BIGINT NOT NULL DEFAULT 1,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE dns_records (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    zone_id             UUID NOT NULL REFERENCES dns_zones(id) ON DELETE CASCADE,
    record_type         dns_record_type NOT NULL,
    name                TEXT NOT NULL,
    value               TEXT NOT NULL,
    ttl                 INTEGER NOT NULL DEFAULT 3600,
    priority             INTEGER, -- for MX/SRV
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_dns_records_zone_id ON dns_records(zone_id);

-- 1-click app/PHP framework deployments (App Manager)
CREATE TABLE applications (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    runtime             app_runtime NOT NULL,
    entry_point         TEXT NOT NULL,
    internal_port       INTEGER NOT NULL,
    proxy_path          TEXT NOT NULL DEFAULT '/',
    process_manager     TEXT NOT NULL DEFAULT 'pm2',
    is_running          BOOLEAN NOT NULL DEFAULT false,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_applications_domain_id ON applications(domain_id);

CREATE TABLE git_deployments (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    repo_url            TEXT NOT NULL,
    branch              TEXT NOT NULL DEFAULT 'main',
    deploy_key_path     TEXT NOT NULL,
    webhook_secret_hash TEXT NOT NULL,
    last_commit_sha     TEXT,
    last_deployed_at    TIMESTAMPTZ,
    auto_deploy         BOOLEAN NOT NULL DEFAULT true
);
CREATE INDEX idx_git_deployments_domain_id ON git_deployments(domain_id);

-- ---------------------------------------------------------------------
-- 4. DATABASES (MySQL/PostgreSQL lifecycle)
-- ---------------------------------------------------------------------

-- db_name is the tenant-facing logical name the user typed (e.g. "wordpress"),
-- unique only within that tenant's own databases — two different accounts may
-- both call a database "wordpress". physical_db_name is the name actually
-- created on the MySQL/Postgres engine (system-generated, conventionally
-- prefixed with the account's username, e.g. "u123_wordpress") and MUST be
-- globally unique per engine since that's an actual server-level constraint.
CREATE TABLE managed_databases (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    engine              db_engine NOT NULL,
    db_name             TEXT NOT NULL,
    physical_db_name    TEXT NOT NULL,
    size_mb             BIGINT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (account_id, engine, db_name),      -- no duplicate logical names within a tenant
    UNIQUE (engine, physical_db_name)          -- global engine-level uniqueness, prevents cross-user collision
);
CREATE INDEX idx_managed_databases_account_id ON managed_databases(account_id);

-- Same logical-vs-physical split as managed_databases, for the same reason:
-- MySQL/Postgres usernames are global on the server, but two tenants should
-- both be able to request a db user called "admin" in the panel UI.
CREATE TABLE database_users (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    engine              db_engine NOT NULL,
    db_username         TEXT NOT NULL,
    physical_username   TEXT NOT NULL,
    password_hash       TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (account_id, engine, db_username),  -- no duplicate logical usernames within a tenant
    UNIQUE (engine, physical_username)         -- global engine-level uniqueness, prevents cross-user collision
);
CREATE INDEX idx_database_users_account_id ON database_users(account_id);

CREATE TABLE database_user_grants (
    database_id         UUID NOT NULL REFERENCES managed_databases(id) ON DELETE CASCADE,
    database_user_id    UUID NOT NULL REFERENCES database_users(id) ON DELETE CASCADE,
    privileges          TEXT[] NOT NULL DEFAULT '{ALL}',
    PRIMARY KEY (database_id, database_user_id)
);

-- phpMyAdmin passwordless signon tokens (short-lived, single-use)
CREATE TABLE db_signon_tokens (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    database_id         UUID NOT NULL REFERENCES managed_databases(id) ON DELETE CASCADE,
    token_hash          TEXT NOT NULL UNIQUE,
    expires_at          TIMESTAMPTZ NOT NULL,
    consumed_at         TIMESTAMPTZ
);
CREATE INDEX idx_db_signon_tokens_expires ON db_signon_tokens(expires_at);

-- ---------------------------------------------------------------------
-- 5. FTP (Pure-FTPd chroot jail)
-- ---------------------------------------------------------------------

CREATE TABLE ftp_accounts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    ftp_username        TEXT NOT NULL UNIQUE,
    password_hash       TEXT NOT NULL,
    home_directory      TEXT NOT NULL, -- always scoped under /home/<username>/
    chroot_jailed       BOOLEAN NOT NULL DEFAULT true,
    quota_mb            BIGINT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_ftp_accounts_account_id ON ftp_accounts(account_id);

-- ---------------------------------------------------------------------
-- 6. MAIL (Exim + Dovecot + deliverability + SpamAssassin)
-- ---------------------------------------------------------------------

CREATE TABLE mail_accounts (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    record_type         mail_record_type NOT NULL DEFAULT 'mailbox',
    local_part          TEXT NOT NULL, -- e.g. "billing" for billing@domain.com
    password_hash       TEXT,          -- NULL for forwarders
    forward_to          TEXT,          -- used when record_type = forwarder/alias
    quota_mb            BIGINT NOT NULL DEFAULT 1024,
    spamassassin_enabled BOOLEAN NOT NULL DEFAULT true,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (domain_id, local_part)
);
CREATE INDEX idx_mail_accounts_domain_id ON mail_accounts(domain_id);

CREATE TABLE mail_deliverability_records (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL UNIQUE REFERENCES domains(id) ON DELETE CASCADE,
    dkim_selector       TEXT NOT NULL DEFAULT 'default',
    dkim_private_key_path TEXT NOT NULL,
    dkim_public_key     TEXT NOT NULL,
    spf_record          TEXT NOT NULL,
    dmarc_record         TEXT NOT NULL,
    generated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Parsed summaries from /var/log/exim_mainlog (not raw log storage)
CREATE TABLE mail_delivery_events (
    id                  BIGSERIAL PRIMARY KEY,
    mail_account_id     UUID REFERENCES mail_accounts(id) ON DELETE SET NULL,
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    direction           TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),
    status              TEXT NOT NULL CHECK (status IN ('delivered', 'deferred', 'bounced', 'rejected', 'spam')),
    remote_host         TEXT,
    message_id          TEXT,
    occurred_at         TIMESTAMPTZ NOT NULL
);
CREATE INDEX idx_mail_delivery_events_domain_id ON mail_delivery_events(domain_id);
CREATE INDEX idx_mail_delivery_events_occurred_at ON mail_delivery_events(occurred_at);

-- ---------------------------------------------------------------------
-- 7. FIREWALL / WAF / MALWARE PROTECTION
-- ---------------------------------------------------------------------

CREATE TABLE firewall_rules (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_by          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    scope               TEXT NOT NULL CHECK (scope IN ('ip_block', 'ip_allow', 'country_block')),
    value               TEXT NOT NULL, -- IP/CIDR or ISO country code
    reason              TEXT,
    expires_at          TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_firewall_rules_scope ON firewall_rules(scope);

CREATE TABLE modsecurity_rulesets (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    ruleset_name        TEXT NOT NULL DEFAULT 'owasp_crs',
    enabled             BOOLEAN NOT NULL DEFAULT true,
    paranoia_level      SMALLINT NOT NULL DEFAULT 1,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (domain_id, ruleset_name)
);

CREATE TABLE malware_scan_results (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    scanner              TEXT NOT NULL CHECK (scanner IN ('clamav', 'maldet')),
    file_path            TEXT NOT NULL,
    threat_signature     TEXT,
    action_taken          TEXT NOT NULL CHECK (action_taken IN ('cleaned', 'quarantined', 'ignored', 'flagged')),
    ai_cleanup_applied     BOOLEAN NOT NULL DEFAULT false,
    scanned_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_malware_scan_results_account_id ON malware_scan_results(account_id);

-- ---------------------------------------------------------------------
-- 8. BANDWIDTH, METERING & BILLING SUPPORT
-- ---------------------------------------------------------------------

-- Aggregated hourly/daily traffic rollups (source: access log parser).
-- Partitioned by month on period_start: at millions of rows/month this keeps
-- each partition small enough for index maintenance, makes retention/purge a
-- cheap DROP TABLE instead of a DELETE, and lets the query planner prune
-- partitions entirely for any query with a period_start range filter.
-- NOTE: PostgreSQL requires the partition key to be part of every unique
-- constraint, so the PK becomes a composite (id, period_start).
CREATE TABLE bandwidth_logs (
    id                  BIGSERIAL,
    domain_id           UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    period_start        TIMESTAMPTZ NOT NULL,
    period_granularity  TEXT NOT NULL CHECK (period_granularity IN ('hourly', 'daily')),
    bytes_in            BIGINT NOT NULL DEFAULT 0,
    bytes_out           BIGINT NOT NULL DEFAULT 0,
    requests            BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id, period_start),
    UNIQUE (domain_id, period_start, period_granularity)
) PARTITION BY RANGE (period_start);

-- Indexes created on the partitioned parent automatically propagate to
-- every existing AND future partition.
CREATE INDEX idx_bandwidth_logs_account_id   ON bandwidth_logs(account_id);
CREATE INDEX idx_bandwidth_logs_period_start ON bandwidth_logs(period_start);

-- Pay-as-you-go metered resource consumption (CPU/RAM/Storage snapshots).
-- Same monthly-partitioning rationale as bandwidth_logs.
CREATE TABLE resource_metering (
    id                  BIGSERIAL,
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    recorded_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    cpu_seconds         NUMERIC(12,2) NOT NULL DEFAULT 0,
    mem_mb_avg          NUMERIC(12,2) NOT NULL DEFAULT 0,
    disk_mb             BIGINT NOT NULL DEFAULT 0,
    io_read_mb          BIGINT NOT NULL DEFAULT 0,
    io_write_mb         BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE INDEX idx_resource_metering_account_id_time ON resource_metering(account_id, recorded_at);

-- ---------------------------------------------------------------------
-- 8a. PARTITION MANAGEMENT
-- ---------------------------------------------------------------------

-- Generic helper: creates the monthly partition for <parent_table> covering
-- the calendar month containing <partition_month>, if it doesn't exist yet.
-- The metering/backup daemon (or a monthly cron/systemd timer) calls this
-- proactively for "next month" so partitions always exist ahead of writes.
CREATE OR REPLACE FUNCTION create_monthly_partition(parent_table TEXT, partition_month DATE)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT := parent_table || '_' || to_char(partition_month, 'YYYY_MM');
    start_date     DATE := date_trunc('month', partition_month);
    end_date       DATE := start_date + INTERVAL '1 month';
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
        partition_name, parent_table, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

-- Bootstrap partitions for the current month and the following two months
-- for both partitioned tables. Ongoing months are created by the helper
-- function above via a scheduled job (see scripts/metering/ensure-partitions.sh).
SELECT create_monthly_partition('bandwidth_logs', date_trunc('month', now())::date);
SELECT create_monthly_partition('bandwidth_logs', (date_trunc('month', now()) + INTERVAL '1 month')::date);
SELECT create_monthly_partition('bandwidth_logs', (date_trunc('month', now()) + INTERVAL '2 month')::date);

SELECT create_monthly_partition('resource_metering', date_trunc('month', now())::date);
SELECT create_monthly_partition('resource_metering', (date_trunc('month', now()) + INTERVAL '1 month')::date);
SELECT create_monthly_partition('resource_metering', (date_trunc('month', now()) + INTERVAL '2 month')::date);

-- Catch-all partitions so a write for an unprovisioned month is rejected
-- loudly in staging but never silently dropped in production; ops is
-- expected to keep these empty by running the monthly job on schedule.
CREATE TABLE bandwidth_logs_default    PARTITION OF bandwidth_logs    DEFAULT;
CREATE TABLE resource_metering_default PARTITION OF resource_metering DEFAULT;

CREATE TABLE billing_usage_summaries (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    billing_period_start DATE NOT NULL,
    billing_period_end   DATE NOT NULL,
    total_cpu_hours       NUMERIC(12,2) NOT NULL DEFAULT 0,
    total_mem_gb_hours     NUMERIC(12,2) NOT NULL DEFAULT 0,
    total_storage_gb_hours NUMERIC(12,2) NOT NULL DEFAULT 0,
    total_bandwidth_gb      NUMERIC(12,2) NOT NULL DEFAULT 0,
    generated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (account_id, billing_period_start, billing_period_end)
);

-- WHMCS provisioning module audit trail (_CreateAccount/_Suspend/etc.)
CREATE TABLE whmcs_provisioning_log (
    id                  BIGSERIAL PRIMARY KEY,
    account_id          UUID REFERENCES accounts(id) ON DELETE SET NULL,
    whmcs_service_id    TEXT NOT NULL,
    action               TEXT NOT NULL CHECK (action IN ('create', 'suspend', 'unsuspend', 'terminate', 'change_package')),
    request_payload      JSONB,
    response_payload     JSONB,
    success               BOOLEAN NOT NULL,
    executed_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_whmcs_log_account_id ON whmcs_provisioning_log(account_id);
CREATE INDEX idx_whmcs_log_service_id ON whmcs_provisioning_log(whmcs_service_id);

-- ---------------------------------------------------------------------
-- 9. BACKUPS (rclone-driven)
-- ---------------------------------------------------------------------

CREATE TABLE backup_destinations (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    destination_type    backup_destination NOT NULL,
    rclone_remote_name  TEXT NOT NULL,
    config_encrypted    TEXT NOT NULL, -- encrypted rclone remote config blob
    is_default          BOOLEAN NOT NULL DEFAULT false,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_backup_destinations_account_id ON backup_destinations(account_id);

CREATE TABLE backup_jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    destination_id      UUID NOT NULL REFERENCES backup_destinations(id) ON DELETE RESTRICT,
    schedule_cron       TEXT NOT NULL DEFAULT '0 2 * * *',
    is_incremental       BOOLEAN NOT NULL DEFAULT true,
    retention_days        INTEGER NOT NULL DEFAULT 30,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_backup_jobs_account_id ON backup_jobs(account_id);

CREATE TABLE backup_runs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    backup_job_id       UUID NOT NULL REFERENCES backup_jobs(id) ON DELETE CASCADE,
    status               backup_status NOT NULL DEFAULT 'queued',
    started_at            TIMESTAMPTZ,
    completed_at           TIMESTAMPTZ,
    size_mb                 BIGINT,
    remote_path              TEXT,
    error_message            TEXT
);
CREATE INDEX idx_backup_runs_job_id ON backup_runs(backup_job_id);
CREATE INDEX idx_backup_runs_status ON backup_runs(status);

-- ---------------------------------------------------------------------
-- 10. PANEL DOCTOR (self-healing) & SYSTEM HEALTH
-- ---------------------------------------------------------------------

-- Baseline SHA-256 file-integrity database for the auto-immune system
CREATE TABLE file_integrity_baselines (
    id                  BIGSERIAL PRIMARY KEY,
    file_path           TEXT NOT NULL UNIQUE,
    sha256_hash          TEXT NOT NULL,
    is_panel_core         BOOLEAN NOT NULL DEFAULT true,
    last_verified_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE file_integrity_incidents (
    id                  BIGSERIAL PRIMARY KEY,
    file_path            TEXT NOT NULL,
    detected_hash          TEXT NOT NULL,
    expected_hash            TEXT NOT NULL,
    action_taken               TEXT NOT NULL CHECK (action_taken IN ('quarantined', 'restored', 'flagged_for_review')),
    detected_at                 TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_file_integrity_incidents_path ON file_integrity_incidents(file_path);

-- Overall service/system health snapshots (litespeed, exim, mysql, etc.)
CREATE TABLE system_health_states (
    id                  BIGSERIAL PRIMARY KEY,
    component            TEXT NOT NULL,
    status                TEXT NOT NULL CHECK (status IN ('healthy', 'degraded', 'down', 'recovering')),
    detail                 TEXT,
    checked_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_system_health_states_component_time ON system_health_states(component, checked_at);

CREATE TABLE self_healing_actions (
    id                  BIGSERIAL PRIMARY KEY,
    component            TEXT NOT NULL,
    issue_detected         TEXT NOT NULL,
    action                   TEXT NOT NULL CHECK (action IN ('systemd_restart', 'memory_flush', 'revert_last_known_good', 'emergency_reset')),
    attempt_number             SMALLINT NOT NULL DEFAULT 1 CHECK (attempt_number <= 3),
    succeeded                    BOOLEAN,
    notified_admin_at              TIMESTAMPTZ,
    created_at                      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_self_healing_actions_component ON self_healing_actions(component);

-- 1-Click Panel Updater run history (systemd transient background jobs)
CREATE TABLE panel_update_runs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_version         TEXT NOT NULL,
    to_version            TEXT NOT NULL,
    status                 update_run_status NOT NULL DEFAULT 'pending',
    log_file_path            TEXT NOT NULL DEFAULT '/var/log/hostgete-update.log',
    checksum_verified           BOOLEAN NOT NULL DEFAULT false,
    rollback_triggered            BOOLEAN NOT NULL DEFAULT false,
    started_at                      TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at                       TIMESTAMPTZ
);

-- Migration wizard runs (cPanel -> HostGete Panel ingestion)
CREATE TABLE migration_jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    source_type          TEXT NOT NULL DEFAULT 'cpanel',
    source_host           TEXT NOT NULL,
    status                  TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'completed', 'failed')),
    files_transferred        BIGINT NOT NULL DEFAULT 0,
    started_at                 TIMESTAMPTZ,
    completed_at                 TIMESTAMPTZ,
    error_message                  TEXT
);
CREATE INDEX idx_migration_jobs_account_id ON migration_jobs(account_id);

-- ---------------------------------------------------------------------
-- 11. AI AGENTS & INCIDENT NOTIFICATIONS
-- ---------------------------------------------------------------------

CREATE TABLE ai_agent_actions (
    id                  BIGSERIAL PRIMARY KEY,
    account_id          UUID REFERENCES accounts(id) ON DELETE SET NULL,
    agent_type            TEXT NOT NULL CHECK (agent_type IN ('user_agent', 'admin_agent')),
    intent_text             TEXT NOT NULL,
    resolved_action            TEXT,
    api_endpoint_called           TEXT,
    success                         BOOLEAN,
    created_at                       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_ai_agent_actions_account_id ON ai_agent_actions(account_id);

CREATE TABLE incident_notifications (
    id                  BIGSERIAL PRIMARY KEY,
    severity             ticket_severity NOT NULL DEFAULT 'info',
    component             TEXT NOT NULL,
    message                 TEXT NOT NULL,
    channel                   TEXT NOT NULL CHECK (channel IN ('telegram', 'whatsapp', 'email')),
    delivered                   BOOLEAN NOT NULL DEFAULT false,
    created_at                    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------
-- 12. AUDIT LOG (cross-cutting, every privileged action)
-- ---------------------------------------------------------------------

CREATE TABLE audit_log (
    id                  BIGSERIAL PRIMARY KEY,
    actor_account_id     UUID REFERENCES accounts(id) ON DELETE SET NULL,
    action_type            TEXT NOT NULL, -- e.g. 'account.suspend', 'domain.create', 'firewall.block_ip'
    target_type              TEXT,
    target_id                  UUID,
    ip_address                   INET,
    metadata                       JSONB,
    created_at                       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_log_actor ON audit_log(actor_account_id);
CREATE INDEX idx_audit_log_target ON audit_log(target_type, target_id);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);

-- ---------------------------------------------------------------------
-- 13. TRIGGERS — keep updated_at columns fresh
-- ---------------------------------------------------------------------

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_accounts_updated_at
    BEFORE UPDATE ON accounts
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- =====================================================================
-- End of Phase 1 schema
-- =====================================================================

HOSTGETE_SCHEMA_EOF

    log "Schema file written ($(wc -l < "${SCHEMA_FILE}") lines)."
}

ingest_schema() {
    log "Ingesting schema into '${DB_NAME}'..."

    # Idempotency guard: if the core 'accounts' table already exists, assume
    # the schema was already ingested by a prior run and skip re-applying.
    local schema_present
    schema_present=$(su - postgres -c "psql -d ${DB_NAME} -tAc \"SELECT 1 FROM information_schema.tables WHERE table_name='accounts'\"")

    if [[ "${schema_present}" == "1" ]]; then
        warn "Schema already present in '${DB_NAME}' — skipping ingestion."
        return
    fi

    if su - postgres -c "psql -v ON_ERROR_STOP=1 -d ${DB_NAME} -f ${SCHEMA_FILE}" >>"${INSTALL_LOG}" 2>&1; then
        log "Schema successfully ingested into '${DB_NAME}'."
    else
        fail "Schema ingestion failed. Check ${INSTALL_LOG} for the failing statement, then re-run the installer."
    fi
}

# =========================================================================
# STEP 4 — SYSTEMD CORE BOOTSTRAP
# =========================================================================

create_runtime_dirs() {
    log "Creating HostGete Panel runtime directories under ${INSTALL_ROOT}..."

    install -d -m 750 "${INSTALL_ROOT}" "${BIN_DIR}" "${CONF_DIR}" \
        "${LOG_DIR}" "${SCRIPTS_DIR}" "${MIGRATIONS_DIR}" "${STATE_DIR}"

    # Dedicated system user for the hostgete-cored daemon (no login shell).
    if ! id -u hostgete >/dev/null 2>&1; then
        useradd --system --no-create-home --shell /usr/sbin/nologin hostgete
        log "Created system user 'hostgete'."
    fi

    chown -R hostgete:hostgete "${LOG_DIR}" "${STATE_DIR}"
}

write_env_file() {
    log "Writing environment configuration to ${ENV_FILE}..."

    API_BEARER_SECRET="$(generate_secret 48)"
    PANEL_ADMIN_PASSWORD="$(generate_secret 20)"
    readonly API_BEARER_SECRET
    readonly PANEL_ADMIN_PASSWORD

    umask 077
    cat > "${ENV_FILE}" <<-ENVEOF
		# HostGete Panel environment configuration
		# Generated by install.sh on $(_ts) — treat as a secret file (0640, root:hostgete).
		HOSTGETE_VERSION=${HOSTGETE_VERSION}
		HOSTGETE_ENV=production

		DB_HOST=${DB_HOST}
		DB_PORT=${DB_PORT}
		DB_NAME=${DB_NAME}
		DB_USER=${DB_USER}
		DB_PASSWORD=${DB_PASSWORD}
		DB_SSLMODE=disable

		API_BEARER_SECRET=${API_BEARER_SECRET}
		LOG_DIR=${LOG_DIR}
		STATE_DIR=${STATE_DIR}

		PANEL_ADMIN_USER=${PANEL_ADMIN_USER}
		PANEL_ADMIN_PASSWORD=${PANEL_ADMIN_PASSWORD}
	ENVEOF

    chown root:hostgete "${ENV_FILE}"
    chmod 640 "${ENV_FILE}"

    log "Environment file written and permissions locked to root:hostgete (0640)."
}

write_systemd_unit() {
    log "Writing systemd unit ${SYSTEMD_UNIT}..."

    cat > "${SYSTEMD_UNIT}" <<-UNITEOF
		[Unit]
		Description=HostGete Panel Core Daemon (hostgete-cored)
		Documentation=https://docs.hostgete.io
		After=network-online.target postgresql.service
		Wants=network-online.target
		StartLimitIntervalSec=60
		StartLimitBurst=3

		[Service]
		Type=simple
		User=hostgete
		Group=hostgete
		WorkingDirectory=${INSTALL_ROOT}
		EnvironmentFile=${ENV_FILE}
		ExecStart=${BIN_DIR}/hostgete-cored
		Restart=on-failure
		RestartSec=5
		# Panel Doctor's self-healing layer additionally caps restarts at 3
		# attempts at the application level before escalating to an admin
		# notification; this unit-level limit is a hard backstop underneath it.

		NoNewPrivileges=true
		ProtectSystem=strict
		ProtectHome=true
		ReadWritePaths=${LOG_DIR} ${STATE_DIR}
		PrivateTmp=true

		StandardOutput=append:${LOG_DIR}/hostgete-cored.log
		StandardError=append:${LOG_DIR}/hostgete-cored.log

		[Install]
		WantedBy=multi-user.target
	UNITEOF

    systemctl daemon-reload
    systemctl enable hostgete-cored.service >>"${INSTALL_LOG}" 2>&1

    if [[ ! -x "${BIN_DIR}/hostgete-cored" ]]; then
        warn "hostgete-cored binary not yet present at ${BIN_DIR}/hostgete-cored — unit is enabled but not started. Deploy the daemon binary (Phase 2) and run: systemctl start hostgete-cored"
    else
        systemctl start hostgete-cored.service
        log "hostgete-cored.service started."
    fi
}

# =========================================================================
# STEP 5 — POST-INSTALL SUMMARY
# =========================================================================

print_summary() {
    local server_ip
    server_ip=$(curl -fsSL -4 --max-time 5 https://ifconfig.me 2>/dev/null || hostname -I | awk '{print $1}')

    local protocol="http"
    local port="7080"

    cat <<-SUMMARY

	================================================================
	 HostGete Panel ${HOSTGETE_VERSION} — Installation Complete
	================================================================

	 Panel URL:        ${protocol}://${server_ip}:${port}/
	 Admin Username:   ${PANEL_ADMIN_USER}
	 Admin Password:   ${PANEL_ADMIN_PASSWORD}

	 Database Name:    ${DB_NAME}
	 Database User:    ${DB_USER}
	 Credentials File: ${ENV_FILE}  (root:hostgete, 0640)

	 Service:          systemctl status hostgete-cored
	 Install Log:      ${INSTALL_LOG}
	 Update Log:       /var/log/hostgete-update.log

	 NOTE: Save the admin password now — it is not printed again.
	       The hostgete-cored binary must be deployed to
	       ${BIN_DIR}/hostgete-cored before the panel becomes reachable.
	================================================================

	SUMMARY
}

# =========================================================================
# MAIN
# =========================================================================

main() {
    mkdir -p "$(dirname "${INSTALL_LOG}")"
    touch "${INSTALL_LOG}"
    chmod 600 "${INSTALL_LOG}"

    log "Starting HostGete Panel installer v${HOSTGETE_VERSION}..."

    require_root
    detect_os
    check_minimum_resources
    check_required_ports

    install_dependencies

    start_postgresql
    provision_database

    write_schema_file
    ingest_schema

    create_runtime_dirs
    write_env_file
    write_systemd_unit

    print_summary
    log "HostGete Panel installation finished successfully."
}

main "$@"