-- PostgreSQL 17 schema (converted from MySQL wg.sql)

CREATE TABLE IF NOT EXISTS users (
    id              BIGSERIAL PRIMARY KEY,
    username        VARCHAR(64)  NOT NULL UNIQUE,
    email           VARCHAR(128) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            VARCHAR(16)  NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
    is_approved     BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS site_status (
    id          BIGSERIAL PRIMARY KEY,
    is_online   BOOLEAN     NOT NULL DEFAULT TRUE,
    message     TEXT        NOT NULL DEFAULT '',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS servers (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    ip              VARCHAR(45)  NOT NULL,
    port            INTEGER      NOT NULL DEFAULT 54321,
    panel_username  VARCHAR(64)  NOT NULL,
    panel_password  VARCHAR(255) NOT NULL,
    status          VARCHAR(16)  NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ,
    inbound_id      INTEGER      NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS servers_simple (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    host        VARCHAR(255) NOT NULL,
    port        INTEGER      NOT NULL,
    country     VARCHAR(255) DEFAULT '?',
    status      VARCHAR(16)  DEFAULT 'active',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS countries (
    id          BIGSERIAL PRIMARY KEY,
    code        VARCHAR(8)   NOT NULL UNIQUE,
    name_ru     VARCHAR(128) NOT NULL,
    name_en     VARCHAR(128) NOT NULL,
    flag        VARCHAR(16)  NOT NULL DEFAULT '',
    sort_order  INTEGER      DEFAULT 0
);

CREATE TABLE IF NOT EXISTS wg_configs (
    id                  BIGSERIAL PRIMARY KEY,
    token               VARCHAR(32) UNIQUE,
    user_id             BIGINT REFERENCES users(id) ON DELETE SET NULL,
    config_content      TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    original_filename   VARCHAR(255) NOT NULL DEFAULT 'wg_config.conf',
    is_demo             BOOLEAN DEFAULT FALSE
);

CREATE INDEX IF NOT EXISTS idx_wg_configs_user_id ON wg_configs(user_id);

CREATE TABLE IF NOT EXISTS wg_config_settings (
    setting_key     VARCHAR(50) PRIMARY KEY,
    setting_value   VARCHAR(10) NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS demo_keys (
    id          BIGSERIAL PRIMARY KEY,
    config_id   BIGINT NOT NULL REFERENCES wg_configs(id) ON DELETE CASCADE,
    token       VARCHAR(32) NOT NULL UNIQUE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS awg_configs (
    id              BIGSERIAL PRIMARY KEY,
    config_name     VARCHAR(100) NOT NULL,
    config_content  TEXT NOT NULL,
    qr_code_url     VARCHAR(512) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS amnezia_configs (
    id                  BIGSERIAL PRIMARY KEY,
    config_name         VARCHAR(255) NOT NULL,
    original_filename   VARCHAR(255) NOT NULL,
    file_path           VARCHAR(512) NOT NULL,
    file_url            VARCHAR(512) NOT NULL,
    config_content      TEXT NOT NULL,
    host                VARCHAR(255) NOT NULL,
    port                INTEGER NOT NULL DEFAULT 51820,
    username            VARCHAR(255),
    password            VARCHAR(255),
    description         TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS vless_configs (
    id                  BIGSERIAL PRIMARY KEY,
    vless_uri           TEXT NOT NULL,
    subscription_url    TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin_uploads (
    id              BIGSERIAL PRIMARY KEY,
    original_name   VARCHAR(255) NOT NULL,
    stored_name     VARCHAR(255) NOT NULL,
    file_path       VARCHAR(512) NOT NULL,
    file_url        VARCHAR(512) NOT NULL DEFAULT '',
    file_size       INTEGER NOT NULL,
    uploaded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_servers (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    server_id   BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    inbound_id  INTEGER NOT NULL,
    protocol    VARCHAR(20) NOT NULL,
    client_id   VARCHAR(64) NOT NULL,
    email       VARCHAR(128) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_user_servers_user ON user_servers(user_id);
CREATE INDEX IF NOT EXISTS idx_user_servers_server ON user_servers(server_id);

CREATE TABLE IF NOT EXISTS license_keys (
    id          BIGSERIAL PRIMARY KEY,
    key_value   CHAR(36) NOT NULL UNIQUE,
    server_id   BIGINT NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    protocol    VARCHAR(20) NOT NULL DEFAULT 'vless',
    client_id   VARCHAR(64) NOT NULL,
    email       VARCHAR(128) NOT NULL DEFAULT 'license@ultravpn.cc',
    used_by     BIGINT REFERENCES users(id) ON DELETE SET NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS rate_limits (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    action      VARCHAR(100) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rate_limits_user_action_time ON rate_limits(user_id, action, created_at);

CREATE TABLE IF NOT EXISTS app_meta (
    key         VARCHAR(64) PRIMARY KEY,
    value       TEXT NOT NULL DEFAULT '',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO site_status (id, is_online, message)
VALUES (1, TRUE, 'Сайт работает в штатном режиме.')
ON CONFLICT (id) DO NOTHING;

INSERT INTO wg_config_settings (setting_key, setting_value) VALUES
    ('show_zip_full', '0'),
    ('show_zip_conf', '1')
ON CONFLICT (setting_key) DO NOTHING;