-- Users
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
username text UNIQUE NOT NULL,
displayname text,
email text UNIQUE,
profilepic text,
type text NOT NULL DEFAULT 'user', -- user | creator | agent
walletcoins bigint DEFAULT 0,
stripeaccountid text,
createdat timestamptz DEFAULT now()
);

-- Rooms
CREATE TABLE rooms (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
title text,
topic text,
hostid uuid REFERENCES users(id),
type text DEFAULT 'public', -- public|private
maxparticipants int DEFAULT 1000,
status text DEFAULT 'scheduled', -- scheduled|live|ended
startedat timestamptz,
scheduledat timestamptz,
created_at timestamptz DEFAULT now()
);

-- Participants
CREATE TABLE roomparticipants (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
roomid uuid REFERENCES rooms(id),
userid uuid REFERENCES users(id),
role text NOT NULL, -- host|cohost|audience|moderator
joinedat timestamptz DEFAULT now(),
left_at timestamptz
);

-- Gifts catalog
CREATE TABLE gifts (
id serial PRIMARY KEY,
name text NOT NULL,
coincost int NOT NULL,
animationmeta jsonb,
created_at timestamptz DEFAULT now()
);

-- Gift transactions
CREATE TABLE gifttransactions (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
fromuserid uuid REFERENCES users(id),
touserid uuid REFERENCES users(id),
roomid uuid REFERENCES rooms(id),
giftid int REFERENCES gifts(id),
coins int NOT NULL,
platformfee numeric(10,2) DEFAULT 0,
agencyfee numeric(10,2) DEFAULT 0,
createdat timestamptz DEFAULT now()
);

-- Agencies
CREATE TABLE agencies (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
name text NOT NULL,
agentuserid uuid REFERENCES users(id),
commissionrate numeric(5,2) DEFAULT 20.0, -- percent
createdat timestamptz DEFAULT now()
);

-- Bookings
CREATE TABLE bookings (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
agencyid uuid REFERENCES agencies(id),
creatorid uuid REFERENCES users(id),
requesterid uuid REFERENCES users(id),
amountcents int,
status text DEFAULT 'pending', -- pending|accepted|declined|completed
created_at timestamptz DEFAULT now()
);

-- Reports
CREATE TABLE reports (
id uuid PRIMARY KEY DEFAULT genrandomuuid(),
reporterid uuid REFERENCES users(id),
targetuserid uuid REFERENCES users(id),
targetroomid uuid REFERENCES rooms(id),
reason text,
status text DEFAULT 'open', -- open|reviewed|actioned
createdat timestamptz DEFAULT now()
);