104 lines
4.2 KiB
PL/PgSQL
104 lines
4.2 KiB
PL/PgSQL
-- Run this SQL in your Supabase SQL Editor to create the database schema
|
|
|
|
-- Enable the UUID extension if not already enabled
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Create users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
balance DECIMAL(10, 2) DEFAULT 1000.00,
|
|
is_admin BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- Create games table
|
|
CREATE TABLE IF NOT EXISTS games (
|
|
id TEXT PRIMARY KEY,
|
|
player1 TEXT NOT NULL,
|
|
player2 TEXT NOT NULL,
|
|
status TEXT DEFAULT 'upcoming' CHECK (status IN ('upcoming', 'in_progress', 'completed')),
|
|
winner TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- Create bets table
|
|
CREATE TABLE IF NOT EXISTS bets (
|
|
id TEXT PRIMARY KEY,
|
|
game_id TEXT NOT NULL REFERENCES games(id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
user_name TEXT NOT NULL,
|
|
amount DECIMAL(10, 2) NOT NULL CHECK (amount > 0),
|
|
condition TEXT NOT NULL,
|
|
certainty INTEGER NOT NULL CHECK (certainty >= 0 AND certainty <= 100),
|
|
yes_tokens INTEGER DEFAULT 0,
|
|
no_tokens INTEGER DEFAULT 0,
|
|
bet_type TEXT DEFAULT 'hedged' CHECK (bet_type IN ('hedged', 'non-hedged')),
|
|
actual_cost DECIMAL(10, 2) NOT NULL,
|
|
platform_fee DECIMAL(10, 2) NOT NULL,
|
|
net_cost DECIMAL(10, 2) NOT NULL,
|
|
is_resolved BOOLEAN DEFAULT false,
|
|
payout_amount DECIMAL(10, 2) DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- Create platform account table
|
|
CREATE TABLE IF NOT EXISTS platform_account (
|
|
id INTEGER PRIMARY KEY DEFAULT 1,
|
|
balance DECIMAL(10, 2) DEFAULT 0.00,
|
|
total_fees DECIMAL(10, 2) DEFAULT 0.00,
|
|
transaction_count INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
CONSTRAINT single_platform_account CHECK (id = 1)
|
|
);
|
|
|
|
-- Insert the initial platform account record
|
|
INSERT INTO platform_account (id, balance, total_fees, transaction_count)
|
|
VALUES (1, 0.00, 0.00, 0)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_bets_game_id ON bets(game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bets_user_id ON bets(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bets_condition ON bets(condition);
|
|
CREATE INDEX IF NOT EXISTS idx_games_status ON games(status);
|
|
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_bets_created_at ON bets(created_at);
|
|
|
|
-- Create updated_at triggers for automatic timestamp updates
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = timezone('utc'::text, now());
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Apply the trigger to all tables
|
|
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_games_updated_at BEFORE UPDATE ON games
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_bets_updated_at BEFORE UPDATE ON bets
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_platform_account_updated_at BEFORE UPDATE ON platform_account
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Enable Row Level Security (RLS) for better security
|
|
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE games ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE bets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE platform_account ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies (allow all operations for now, you can restrict later)
|
|
CREATE POLICY "Allow all operations on users" ON users FOR ALL USING (true);
|
|
CREATE POLICY "Allow all operations on games" ON games FOR ALL USING (true);
|
|
CREATE POLICY "Allow all operations on bets" ON bets FOR ALL USING (true);
|
|
CREATE POLICY "Allow all operations on platform_account" ON platform_account FOR ALL USING (true); |