-- ============================================================
-- UID TRACKER SYSTEM — PostgreSQL Schema
-- Production-ready with indexes
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ══════════════════════════════════════════════
-- TABLE: users
-- ══════════════════════════════════════════════
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    telegram_id     BIGINT      UNIQUE NOT NULL,
    username        VARCHAR(64)  DEFAULT '',
    full_name       VARCHAR(128) DEFAULT '',
    api_key         VARCHAR(64)  UNIQUE DEFAULT md5(random()::text),
    role            VARCHAR(16)  NOT NULL DEFAULT 'user',   -- user | admin
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
    uid_quota       INT          NOT NULL DEFAULT 500,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_telegram_id ON users(telegram_id);
CREATE INDEX idx_users_api_key     ON users(api_key);
CREATE INDEX idx_users_role        ON users(role);

-- ══════════════════════════════════════════════
-- TABLE: uids
-- ══════════════════════════════════════════════
CREATE TABLE uids (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    uid             VARCHAR(32)  NOT NULL,
    note            VARCHAR(256) DEFAULT '',
    price           NUMERIC(12,2) DEFAULT 0,
    status          VARCHAR(16)  NOT NULL DEFAULT 'pending',  -- pending | live | die | error
    check_count     INT          NOT NULL DEFAULT 0,
    last_checked_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_user_uid UNIQUE (user_id, uid)
);

CREATE INDEX idx_uids_user_id         ON uids(user_id);
CREATE INDEX idx_uids_status          ON uids(status);
CREATE INDEX idx_uids_uid             ON uids(uid);
CREATE INDEX idx_uids_last_checked_at ON uids(last_checked_at);
CREATE INDEX idx_uids_user_status     ON uids(user_id, status);

-- ══════════════════════════════════════════════
-- TABLE: jobs
-- ══════════════════════════════════════════════
CREATE TABLE jobs (
    id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    uid_id          BIGINT       NOT NULL REFERENCES uids(id) ON DELETE CASCADE,
    user_id         BIGINT       NOT NULL REFERENCES users(id),
    worker_id       UUID,                                       -- assigned worker
    status          VARCHAR(16)  NOT NULL DEFAULT 'queued',     -- queued | running | done | failed | timeout
    priority        SMALLINT     NOT NULL DEFAULT 5,            -- 1=high, 10=low
    retry_count     SMALLINT     NOT NULL DEFAULT 0,
    max_retries     SMALLINT     NOT NULL DEFAULT 3,
    result          VARCHAR(16),                                -- live | die | error
    error_message   TEXT,
    queued_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    started_at      TIMESTAMPTZ,
    finished_at     TIMESTAMPTZ,
    timeout_at      TIMESTAMPTZ  DEFAULT NOW() + INTERVAL '5 minutes',
    idempotency_key VARCHAR(64)  UNIQUE                         -- prevent duplicate jobs
);

CREATE INDEX idx_jobs_uid_id     ON jobs(uid_id);
CREATE INDEX idx_jobs_user_id    ON jobs(user_id);
CREATE INDEX idx_jobs_status     ON jobs(status);
CREATE INDEX idx_jobs_worker_id  ON jobs(worker_id);
CREATE INDEX idx_jobs_queued_at  ON jobs(queued_at);
CREATE INDEX idx_jobs_timeout_at ON jobs(timeout_at) WHERE status IN ('queued','running');

-- ══════════════════════════════════════════════
-- TABLE: workers
-- ══════════════════════════════════════════════
CREATE TABLE workers (
    id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    hostname        VARCHAR(128) NOT NULL,
    api_key         VARCHAR(64)  UNIQUE NOT NULL,
    status          VARCHAR(16)  NOT NULL DEFAULT 'offline',    -- online | offline | draining
    jobs_processed  INT          NOT NULL DEFAULT 0,
    jobs_failed     INT          NOT NULL DEFAULT 0,
    last_heartbeat  TIMESTAMPTZ,
    registered_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    metadata        JSONB        DEFAULT '{}'
);

CREATE INDEX idx_workers_status        ON workers(status);
CREATE INDEX idx_workers_last_heartbeat ON workers(last_heartbeat);

-- ══════════════════════════════════════════════
-- TABLE: uid_history
-- ══════════════════════════════════════════════
CREATE TABLE uid_history (
    id              BIGSERIAL    PRIMARY KEY,
    uid_id          BIGINT       NOT NULL REFERENCES uids(id) ON DELETE CASCADE,
    job_id          UUID         REFERENCES jobs(id),
    old_status      VARCHAR(16),
    new_status      VARCHAR(16)  NOT NULL,
    worker_id       UUID,
    proxy_used      VARCHAR(128),
    checked_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_uid_history_uid_id     ON uid_history(uid_id);
CREATE INDEX idx_uid_history_checked_at ON uid_history(checked_at);

-- ══════════════════════════════════════════════
-- TABLE: logs
-- ══════════════════════════════════════════════
CREATE TABLE logs (
    id          BIGSERIAL    PRIMARY KEY,
    level       VARCHAR(8)   NOT NULL DEFAULT 'INFO',  -- DEBUG | INFO | WARN | ERROR
    source      VARCHAR(64)  NOT NULL,                 -- api | worker | bot
    message     TEXT         NOT NULL,
    metadata    JSONB        DEFAULT '{}',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_logs_level      ON logs(level);
CREATE INDEX idx_logs_source     ON logs(source);
CREATE INDEX idx_logs_created_at ON logs(created_at);

-- Partition logs by month for large scale (optional)
-- CREATE TABLE logs_2026_02 PARTITION OF logs FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- ══════════════════════════════════════════════
-- FUNCTION: auto-update updated_at
-- ══════════════════════════════════════════════
CREATE OR REPLACE FUNCTION update_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 FUNCTION update_updated_at();

CREATE TRIGGER trg_uids_updated_at BEFORE UPDATE ON uids
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ══════════════════════════════════════════════
-- SEED: default admin
-- ══════════════════════════════════════════════
INSERT INTO users (telegram_id, username, full_name, role, api_key)
VALUES (0, 'admin', 'System Admin', 'admin', 'ADMIN_API_KEY_CHANGE_ME')
ON CONFLICT DO NOTHING;
