database/schema.sql

```sql
-- Users Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    phone VARCHAR(11) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    verified BOOLEAN DEFAULT FALSE,
    age_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'banned'))
);

-- Wallet Transactions Table
CREATE TABLE wallet_transactions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount DECIMAL(12,2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL CHECK (transaction_type IN ('deposit', 'withdrawal', 'win', 'bet', 'bonus', 'penalty')),
    payment_method VARCHAR(20),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed', 'reversed')),
    reference_id VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE,
    notes TEXT
);

-- Game History Table
CREATE TABLE game_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    game_type VARCHAR(50) NOT NULL,
    bet_amount DECIMAL(12,2) NOT NULL,
    win_amount DECIMAL(12,2),
    outcome VARCHAR(20) CHECK (outcome IN ('win', 'loss', 'draw', 'pending')),
    game_details JSONB,
    session_id VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE
);

-- Responsible Gaming Settings Table
CREATE TABLE responsible_gaming_settings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    deposit_limit_daily DECIMAL(12,2) DEFAULT 5000.00,
    deposit_limit_weekly DECIMAL(12,2) DEFAULT 20000.00,
    deposit_limit_monthly DECIMAL(12,2) DEFAULT 50000.00,
    loss_limit_daily DECIMAL(12,2),
    loss_limit_weekly DECIMAL(12,2),
    session_time_limit_minutes INTEGER DEFAULT 60,
    self_exclusion BOOLEAN DEFAULT FALSE,
    self_exclusion_start TIMESTAMP WITH TIME ZONE,
    self_exclusion_end TIMESTAMP WITH TIME ZONE,
    cooling_off_period_days INTEGER,
    reality_check_minutes INTEGER DEFAULT 60,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for better performance
CREATE INDEX idx_wallet_transactions_user_id ON wallet_transactions(user_id);
CREATE INDEX idx_wallet_transactions_created_at ON wallet_transactions(created_at);
CREATE INDEX idx_game_history_user_id ON game_history(user_id);
CREATE INDEX idx_game_history_created_at ON game_history(created_at);
CREATE UNIQUE INDEX idx_responsible_gaming_user_id ON responsible_gaming_settings(user_id);
```
___METADATA_START___
{"repoId":"azimpolcu/dhaka-dice-dynamo","isNew":false,"userName":"azimpolcu"}
___METADATA_END___