sql/schema.sql

-- ==============================================================================
-- 🗄️ GACHA BOT V3 SCHEMA
-- 🛡️ Includes strict ON DELETE CASCADE and SET NULL rules for data integrity
-- ==============================================================================

-- 1. BASE TABLES (No Foreign Key Dependencies)

CREATE TABLE IF NOT EXISTS users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    coins INT DEFAULT 0,
    dust INT DEFAULT 0,
    time_winders INT DEFAULT 0,
    last_daily TIMESTAMP WITH TIME ZONE,
    last_drop TIMESTAMP WITH TIME ZONE,
    weekly_auctions INT DEFAULT 0,
    auction_reset_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    unlocked_titles TEXT,
    equipped_title VARCHAR(255),
    profile_dump_msg_id BIGINT,
    showcase_id INT, 
    profile_slot_1 INT,
    profile_slot_2 INT,
    profile_slot_3 INT,
    profile_slot_4 INT,
    profile_slot_5 INT,
    profile_slot_6 INT,
    profile_slot_7 INT,
    profile_slot_8 INT
);

CREATE TABLE IF NOT EXISTS characters (
    char_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    series VARCHAR(255),
    rarity VARCHAR(50) NOT NULL,
    element VARCHAR(50),
    atk INT DEFAULT 50,
    def INT DEFAULT 50,
    image_url TEXT,
    c6_gif_url TEXT,
    custom_frame_url TEXT,
    acquired_count INT DEFAULT 0,
    source VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS banner_rates (
    rarity VARCHAR(50) PRIMARY KEY,
    drop_rate FLOAT NOT NULL
);

-- Insert the default banner rates immediately
INSERT INTO banner_rates (rarity, drop_rate) 
VALUES ('C', 50.0), ('R', 30.0), ('SR', 15.0), ('SSR', 4.0), ('UR', 1.0)
ON CONFLICT (rarity) DO NOTHING;


-- 2. DEPENDENT TABLES (Core Systems)

CREATE TABLE IF NOT EXISTS user_characters (
    instance_id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    char_id INT REFERENCES characters(char_id) ON DELETE CASCADE,
    print_num INT NOT NULL,
    level INT DEFAULT 1,
    constellation INT DEFAULT 0,
    locked BOOLEAN DEFAULT FALSE,
    dump_msg_id BIGINT,
    obtained_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- We add the foreign keys for the user profile slots AFTER user_characters exists 
-- to prevent circular dependency errors during the first boot.
ALTER TABLE users
    DROP CONSTRAINT IF EXISTS fk_showcase,
    DROP CONSTRAINT IF EXISTS fk_slot_1,
    DROP CONSTRAINT IF EXISTS fk_slot_2,
    DROP CONSTRAINT IF EXISTS fk_slot_3,
    DROP CONSTRAINT IF EXISTS fk_slot_4,
    DROP CONSTRAINT IF EXISTS fk_slot_5,
    DROP CONSTRAINT IF EXISTS fk_slot_6,
    DROP CONSTRAINT IF EXISTS fk_slot_7,
    DROP CONSTRAINT IF EXISTS fk_slot_8;

ALTER TABLE users
    ADD CONSTRAINT fk_showcase FOREIGN KEY (showcase_id) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_1 FOREIGN KEY (profile_slot_1) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_2 FOREIGN KEY (profile_slot_2) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_3 FOREIGN KEY (profile_slot_3) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_4 FOREIGN KEY (profile_slot_4) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_5 FOREIGN KEY (profile_slot_5) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_6 FOREIGN KEY (profile_slot_6) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_7 FOREIGN KEY (profile_slot_7) REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_slot_8 FOREIGN KEY (profile_slot_8) REFERENCES user_characters(instance_id) ON DELETE SET NULL;


-- 3. SECONDARY SYSTEMS (Combat, Economy, Social)

CREATE TABLE IF NOT EXISTS user_teams (
    user_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
    slot_1 INT REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    slot_2 INT REFERENCES user_characters(instance_id) ON DELETE SET NULL,
    slot_3 INT REFERENCES user_characters(instance_id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS expeditions (
    instance_id INT PRIMARY KEY REFERENCES user_characters(instance_id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    duration_hours INT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS market_listings (
    listing_id SERIAL PRIMARY KEY,
    seller_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    instance_id INT UNIQUE REFERENCES user_characters(instance_id) ON DELETE CASCADE,
    price INT NOT NULL,
    currency_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS active_auctions (
    auction_id SERIAL PRIMARY KEY,
    seller_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    instance_id INT UNIQUE REFERENCES user_characters(instance_id) ON DELETE CASCADE,
    starting_bid INT NOT NULL,
    current_bid INT DEFAULT 0,
    highest_bidder_id BIGINT REFERENCES users(user_id) ON DELETE SET NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    currency_type VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS global_bounties (
    target_id BIGINT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
    coin_reward INT NOT NULL,
    dust_reward INT NOT NULL,
    power_level INT NOT NULL,
    tier VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS bounty_claims (
    id SERIAL PRIMARY KEY,
    target_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    hunter_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    claim_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(target_id, hunter_id)
);

CREATE TABLE IF NOT EXISTS user_mail (
    mail_id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    coins INT DEFAULT 0,
    dust INT DEFAULT 0,
    char_id INT REFERENCES characters(char_id) ON DELETE SET NULL,
    is_claimed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_wishlist (
    id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    char_id INT REFERENCES characters(char_id) ON DELETE CASCADE,
    UNIQUE(user_id, char_id)
);

CREATE TABLE IF NOT EXISTS user_pity (
    user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE,
    banner_id INT NOT NULL,
    pull_count INT DEFAULT 0,
    PRIMARY KEY (user_id, banner_id)
);