-- Pulse Launch PHP — PostgreSQL schema

CREATE TYPE token_status AS ENUM ('PENDING', 'ACTIVE', 'GRADUATED', 'MIGRATED', 'BANNED');
CREATE TYPE trade_type AS ENUM ('BUY', 'SELL');
CREATE TYPE report_status AS ENUM ('OPEN', 'REVIEWING', 'RESOLVED', 'DISMISSED');
CREATE TYPE notification_type AS ENUM ('TRADE', 'COMMENT', 'FAVORITE', 'GRADUATION', 'SYSTEM', 'MENTION');

CREATE TABLE users (
    id              VARCHAR(32) PRIMARY KEY,
    wallet_address  VARCHAR(64) UNIQUE NOT NULL,
    username        VARCHAR(64) UNIQUE,
    display_name    VARCHAR(128),
    bio             TEXT,
    avatar_url      TEXT,
    is_admin        BOOLEAN DEFAULT FALSE,
    is_banned       BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE wallets (
    id          VARCHAR(32) PRIMARY KEY,
    user_id     VARCHAR(32) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    address     VARCHAR(64) UNIQUE NOT NULL,
    is_primary  BOOLEAN DEFAULT FALSE,
    label       VARCHAR(64),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tokens (
    id                      VARCHAR(32) PRIMARY KEY,
    mint_address            VARCHAR(64) UNIQUE NOT NULL,
    name                    VARCHAR(64) NOT NULL,
    symbol                  VARCHAR(16) NOT NULL,
    description             TEXT,
    image_url               TEXT,
    website_url             TEXT,
    twitter_url             TEXT,
    telegram_url            TEXT,
    discord_url             TEXT,
    creator_id              VARCHAR(32) NOT NULL REFERENCES users(id),
    virtual_sol_reserves    NUMERIC(20,9) DEFAULT 30,
    virtual_token_reserves  NUMERIC(30,0) DEFAULT 1073000000,
    real_sol_reserves       NUMERIC(20,9) DEFAULT 0,
    real_token_reserves     NUMERIC(30,0) DEFAULT 0,
    total_supply            NUMERIC(30,0) DEFAULT 1000000000,
    market_cap              NUMERIC(20,9) DEFAULT 0,
    volume_24h              NUMERIC(20,9) DEFAULT 0,
    volume_total            NUMERIC(20,9) DEFAULT 0,
    price                   NUMERIC(20,12) DEFAULT 0,
    holders_count           INT DEFAULT 0,
    trades_count            INT DEFAULT 0,
    graduation_threshold    NUMERIC(20,9) DEFAULT 85,
    is_graduated            BOOLEAN DEFAULT FALSE,
    graduated_at            TIMESTAMPTZ,
    raydium_pool_address    VARCHAR(64),
    status                  token_status DEFAULT 'ACTIVE',
    launch_fee_paid         NUMERIC(20,9) DEFAULT 0,
    created_at              TIMESTAMPTZ DEFAULT NOW(),
    updated_at              TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE trades (
    id            VARCHAR(32) PRIMARY KEY,
    token_id      VARCHAR(32) NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
    user_id       VARCHAR(32) NOT NULL REFERENCES users(id),
    type          trade_type NOT NULL,
    sol_amount    NUMERIC(20,9) NOT NULL,
    token_amount  NUMERIC(30,0) NOT NULL,
    price         NUMERIC(20,12) NOT NULL,
    fee           NUMERIC(20,9) DEFAULT 0,
    signature     VARCHAR(128) UNIQUE NOT NULL,
    slot          BIGINT,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE holders (
    id              VARCHAR(32) PRIMARY KEY,
    token_id        VARCHAR(32) NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
    wallet_address  VARCHAR(64) NOT NULL,
    balance         NUMERIC(30,0) NOT NULL,
    percentage      NUMERIC(10,6) DEFAULT 0,
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(token_id, wallet_address)
);

CREATE TABLE comments (
    id          VARCHAR(32) PRIMARY KEY,
    token_id    VARCHAR(32) NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
    user_id     VARCHAR(32) NOT NULL REFERENCES users(id),
    content     TEXT NOT NULL,
    is_hidden   BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE favorites (
    id          VARCHAR(32) PRIMARY KEY,
    user_id     VARCHAR(32) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_id    VARCHAR(32) NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, token_id)
);

CREATE TABLE notifications (
    id          VARCHAR(32) PRIMARY KEY,
    user_id     VARCHAR(32) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type        notification_type NOT NULL,
    title       VARCHAR(255) NOT NULL,
    message     TEXT NOT NULL,
    data        JSONB,
    is_read     BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE reports (
    id            VARCHAR(32) PRIMARY KEY,
    reporter_id   VARCHAR(32) NOT NULL REFERENCES users(id),
    target_type   VARCHAR(32) NOT NULL,
    target_id     VARCHAR(64) NOT NULL,
    reason        VARCHAR(255) NOT NULL,
    description   TEXT,
    status        report_status DEFAULT 'OPEN',
    resolved_by   VARCHAR(32),
    resolved_at   TIMESTAMPTZ,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE admins (
    id          VARCHAR(32) PRIMARY KEY,
    user_id     VARCHAR(32) UNIQUE NOT NULL,
    role        VARCHAR(32) DEFAULT 'moderator',
    permissions JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE transactions (
    id            VARCHAR(32) PRIMARY KEY,
    token_id      VARCHAR(32) REFERENCES tokens(id),
    signature     VARCHAR(128) UNIQUE NOT NULL,
    type          VARCHAR(32) NOT NULL,
    from_address  VARCHAR(64),
    to_address    VARCHAR(64),
    amount        NUMERIC(20,9) NOT NULL,
    fee           NUMERIC(20,9) DEFAULT 0,
    status        VARCHAR(32) DEFAULT 'confirmed',
    slot          BIGINT,
    block_time    TIMESTAMPTZ,
    raw           JSONB,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE system_settings (
    id          VARCHAR(32) PRIMARY KEY,
    key         VARCHAR(64) UNIQUE NOT NULL,
    value       JSONB NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tokens_creator ON tokens(creator_id);
CREATE INDEX idx_tokens_status ON tokens(status);
CREATE INDEX idx_tokens_created ON tokens(created_at DESC);
CREATE INDEX idx_trades_token ON trades(token_id, created_at DESC);
CREATE INDEX idx_users_wallet ON users(wallet_address);