-- Panel hosting control panel — initial schema

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "citext";

-- ─── Enums ───────────────────────────────────────────────────────────────────

CREATE TYPE user_status AS ENUM ('active', 'suspended', 'pending');
CREATE TYPE user_role AS ENUM ('superadmin', 'admin', 'reseller', 'user');

CREATE TYPE server_status AS ENUM ('online', 'offline', 'maintenance', 'provisioning');
CREATE TYPE site_status AS ENUM ('active', 'suspended', 'creating', 'deleting', 'error');
CREATE TYPE ssl_type AS ENUM ('letsencrypt', 'custom', 'self_signed');
CREATE TYPE ssl_status AS ENUM ('pending', 'active', 'expired', 'error');
CREATE TYPE db_engine AS ENUM ('mysql', 'postgresql');
CREATE TYPE backup_type AS ENUM ('full', 'files', 'database');
CREATE TYPE backup_status AS ENUM ('pending', 'running', 'completed', 'failed');
CREATE TYPE job_status AS ENUM ('pending', 'running', 'completed', 'failed', 'cancelled');
CREATE TYPE dns_record_type AS ENUM ('A', 'AAAA', 'CNAME', 'MX', 'TXT', 'NS', 'SRV', 'PTR');

-- ─── Users & auth ────────────────────────────────────────────────────────────

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    email       CITEXT NOT NULL UNIQUE,
    username    VARCHAR(64) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role        user_role NOT NULL DEFAULT 'user',
    status      user_status NOT NULL DEFAULT 'pending',
    locale      VARCHAR(10) NOT NULL DEFAULT 'ru',
    timezone    VARCHAR(64) NOT NULL DEFAULT 'Europe/Moscow',
    last_login_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE sessions (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT NOT NULL UNIQUE,
    ip_address  INET,
    user_agent  TEXT,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

CREATE TABLE api_keys (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        VARCHAR(128) NOT NULL,
    key_hash    TEXT NOT NULL UNIQUE,
    key_prefix  VARCHAR(16) NOT NULL,
    last_used_at TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);

-- ─── Servers (managed nodes) ─────────────────────────────────────────────────

CREATE TABLE servers (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    name            VARCHAR(128) NOT NULL,
    hostname        VARCHAR(255) NOT NULL,
    ip_address      INET NOT NULL,
    ssh_port        INT NOT NULL DEFAULT 22,
    status          server_status NOT NULL DEFAULT 'provisioning',
    agent_token_hash TEXT,
    agent_version   VARCHAR(32),
    os_info         JSONB NOT NULL DEFAULT '{}',
    resources       JSONB NOT NULL DEFAULT '{}',
    settings        JSONB NOT NULL DEFAULT '{}',
    last_seen_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_servers_hostname ON servers(hostname);

-- User access to servers (multi-tenant / reseller model)
CREATE TABLE server_access (
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    server_id   BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    can_manage  BOOLEAN NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, server_id)
);

-- ─── Sites (websites) ────────────────────────────────────────────────────────

CREATE TABLE sites (
    id              BIGSERIAL PRIMARY KEY,
    uuid            UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    server_id       BIGINT NOT NULL REFERENCES servers(id) ON DELETE RESTRICT,
    owner_id        BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    name            VARCHAR(128) NOT NULL,
    document_root   VARCHAR(512) NOT NULL,
    php_version     VARCHAR(16),
    status          site_status NOT NULL DEFAULT 'creating',
    settings        JSONB NOT NULL DEFAULT '{}',
    disk_quota_mb   INT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (server_id, name)
);

CREATE INDEX idx_sites_owner_id ON sites(owner_id);
CREATE INDEX idx_sites_server_id ON sites(server_id);

CREATE TABLE domains (
    id          BIGSERIAL PRIMARY KEY,
    site_id     BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    domain      CITEXT NOT NULL,
    is_primary  BOOLEAN NOT NULL DEFAULT false,
    ssl_enabled BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (domain)
);

CREATE INDEX idx_domains_site_id ON domains(site_id);

-- Only one primary domain per site
CREATE UNIQUE INDEX idx_domains_site_primary
    ON domains(site_id) WHERE is_primary = true;

-- ─── Databases ───────────────────────────────────────────────────────────────

CREATE TABLE databases (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    site_id     BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    server_id   BIGINT NOT NULL REFERENCES servers(id) ON DELETE RESTRICT,
    name        VARCHAR(64) NOT NULL,
    engine      db_engine NOT NULL DEFAULT 'mysql',
    charset     VARCHAR(32) NOT NULL DEFAULT 'utf8mb4',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (server_id, engine, name)
);

CREATE INDEX idx_databases_site_id ON databases(site_id);

CREATE TABLE database_users (
    id              BIGSERIAL PRIMARY KEY,
    database_id     BIGINT NOT NULL REFERENCES databases(id) ON DELETE CASCADE,
    username        VARCHAR(64) NOT NULL,
    password_hash   TEXT NOT NULL,
    privileges      TEXT[] NOT NULL DEFAULT '{ALL}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (database_id, username)
);

-- ─── FTP ─────────────────────────────────────────────────────────────────────

CREATE TABLE ftp_accounts (
    id              BIGSERIAL PRIMARY KEY,
    site_id         BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    username        VARCHAR(64) NOT NULL,
    password_hash   TEXT NOT NULL,
    home_path       VARCHAR(512) NOT NULL,
    quota_mb        INT,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (site_id, username)
);

-- ─── SSL certificates ────────────────────────────────────────────────────────

CREATE TABLE ssl_certificates (
    id          BIGSERIAL PRIMARY KEY,
    domain_id   BIGINT NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    type        ssl_type NOT NULL,
    status      ssl_status NOT NULL DEFAULT 'pending',
    issuer      VARCHAR(255),
    cert_pem    TEXT,
    key_pem     TEXT,
    chain_pem   TEXT,
    issued_at   TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ,
    auto_renew  BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_ssl_certificates_domain_id ON ssl_certificates(domain_id);
CREATE INDEX idx_ssl_certificates_expires_at ON ssl_certificates(expires_at);

-- ─── Mail ────────────────────────────────────────────────────────────────────

CREATE TABLE mail_domains (
    id          BIGSERIAL PRIMARY KEY,
    site_id     BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    domain      CITEXT NOT NULL UNIQUE,
    dkim_enabled BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE mail_accounts (
    id              BIGSERIAL PRIMARY KEY,
    mail_domain_id  BIGINT NOT NULL REFERENCES mail_domains(id) ON DELETE CASCADE,
    email           CITEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    quota_mb        INT NOT NULL DEFAULT 1024,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_mail_accounts_mail_domain_id ON mail_accounts(mail_domain_id);

-- ─── DNS ─────────────────────────────────────────────────────────────────────

CREATE TABLE dns_zones (
    id          BIGSERIAL PRIMARY KEY,
    server_id   BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    domain      CITEXT NOT NULL,
    serial      BIGINT NOT NULL DEFAULT 1,
    ttl         INT NOT NULL DEFAULT 3600,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (server_id, domain)
);

CREATE TABLE dns_records (
    id          BIGSERIAL PRIMARY KEY,
    zone_id     BIGINT NOT NULL REFERENCES dns_zones(id) ON DELETE CASCADE,
    type        dns_record_type NOT NULL,
    name        VARCHAR(255) NOT NULL DEFAULT '@',
    content     TEXT NOT NULL,
    ttl         INT NOT NULL DEFAULT 3600,
    priority    INT,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_dns_records_zone_id ON dns_records(zone_id);

-- ─── Cron jobs ───────────────────────────────────────────────────────────────

CREATE TABLE cron_jobs (
    id          BIGSERIAL PRIMARY KEY,
    site_id     BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    schedule    VARCHAR(128) NOT NULL,
    command     TEXT NOT NULL,
    run_as      VARCHAR(64) NOT NULL DEFAULT 'www-data',
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_cron_jobs_site_id ON cron_jobs(site_id);

-- ─── Backups ─────────────────────────────────────────────────────────────────

CREATE TABLE backups (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    site_id     BIGINT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    type        backup_type NOT NULL,
    status      backup_status NOT NULL DEFAULT 'pending',
    storage_path VARCHAR(1024),
    size_bytes  BIGINT,
    error_message TEXT,
    started_at  TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_backups_site_id ON backups(site_id);
CREATE INDEX idx_backups_status ON backups(status);

-- ─── Async job queue (panel → agent tasks) ───────────────────────────────────

CREATE TABLE jobs (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    server_id   BIGINT REFERENCES servers(id) ON DELETE SET NULL,
    site_id     BIGINT REFERENCES sites(id) ON DELETE SET NULL,
    user_id     BIGINT REFERENCES users(id) ON DELETE SET NULL,
    type        VARCHAR(64) NOT NULL,
    payload     JSONB NOT NULL DEFAULT '{}',
    status      job_status NOT NULL DEFAULT 'pending',
    result      JSONB,
    error_message TEXT,
    attempts    INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 3,
    scheduled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at  TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_jobs_status_scheduled ON jobs(status, scheduled_at)
    WHERE status IN ('pending', 'running');
CREATE INDEX idx_jobs_server_id ON jobs(server_id);

-- ─── Audit log ───────────────────────────────────────────────────────────────

CREATE TABLE audit_logs (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT REFERENCES users(id) ON DELETE SET NULL,
    action      VARCHAR(64) NOT NULL,
    resource    VARCHAR(64) NOT NULL,
    resource_id BIGINT,
    details     JSONB NOT NULL DEFAULT '{}',
    ip_address  INET,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource, resource_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);

-- ─── updated_at trigger ──────────────────────────────────────────────────────

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

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_servers_updated_at
    BEFORE UPDATE ON servers FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_sites_updated_at
    BEFORE UPDATE ON sites FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_ftp_accounts_updated_at
    BEFORE UPDATE ON ftp_accounts FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_ssl_certificates_updated_at
    BEFORE UPDATE ON ssl_certificates FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_mail_accounts_updated_at
    BEFORE UPDATE ON mail_accounts FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_dns_zones_updated_at
    BEFORE UPDATE ON dns_zones FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_dns_records_updated_at
    BEFORE UPDATE ON dns_records FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

CREATE TRIGGER trg_cron_jobs_updated_at
    BEFORE UPDATE ON cron_jobs FOR EACH ROW EXECUTE PROCEDURE set_updated_at();