-- Final local schema and migration -- Generated: 2025-12-09T19:57:19.113Z -- Intended for local Postgres (no auth.users dependency). Drops existing objects and recreates schema. -- === DROP === DROP TABLE IF EXISTS room_messages CASCADE; DROP TABLE IF EXISTS logs CASCADE; DROP TABLE IF EXISTS codes CASCADE; DROP TABLE IF EXISTS users CASCADE; DROP TABLE IF EXISTS rooms CASCADE; -- === EXTENSIONS === CREATE EXTENSION IF NOT EXISTS "pgcrypto"; CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- === ROOMS === CREATE TABLE rooms ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR, description VARCHAR(255), status INTEGER DEFAULT 1, date_created TIMESTAMP DEFAULT NOW(), date_last_message TIMESTAMP, updated_at TIMESTAMP DEFAULT NOW(), creator_id UUID ); -- === USERS (local) === CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR UNIQUE NOT NULL, token VARCHAR UNIQUE NOT NULL DEFAULT gen_random_uuid()::text, status INTEGER DEFAULT 1, fname VARCHAR NOT NULL, sname VARCHAR NOT NULL, sex INTEGER, nickname VARCHAR, birth_date DATE, photo_url VARCHAR, lang VARCHAR DEFAULT 'cs', created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() ); -- === CODES === CREATE TABLE codes ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), code VARCHAR(5) UNIQUE NOT NULL, linked_to UUID, used INTEGER, user_id UUID, -- optional: user who claimed the code room_id UUID, -- room to which code belongs pin_hash VARCHAR, session_hash VARCHAR, date_first TIMESTAMP, date_last TIMESTAMP, used_count INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT NOW() ); -- === ROOM MESSAGES === CREATE TABLE room_messages ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), room_id UUID, code_id UUID, user_id UUID, content TEXT NOT NULL, image_url VARCHAR, created_at TIMESTAMP DEFAULT NOW() ); -- === LOGS === CREATE TABLE logs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), created_at TIMESTAMP DEFAULT NOW(), lang VARCHAR, ip VARCHAR, user_agent VARCHAR, module VARCHAR, operation VARCHAR, data JSONB, error TEXT, level VARCHAR ); -- === INDEXES === CREATE INDEX idx_codes_code ON codes(code); CREATE INDEX idx_codes_used ON codes(used); CREATE INDEX idx_codes_room_id ON codes(room_id); CREATE INDEX idx_room_messages_room_id ON room_messages(room_id); CREATE INDEX idx_room_messages_created_at ON room_messages(created_at); CREATE INDEX idx_logs_created_at ON logs(created_at); CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_status ON users(status); -- === FOREIGN KEYS === ALTER TABLE codes ADD CONSTRAINT fk_codes_linked_to FOREIGN KEY (linked_to) REFERENCES codes(id); ALTER TABLE codes ADD CONSTRAINT fk_codes_user FOREIGN KEY (user_id) REFERENCES users(id); ALTER TABLE codes ADD CONSTRAINT fk_codes_room FOREIGN KEY (room_id) REFERENCES rooms(id); ALTER TABLE room_messages ADD CONSTRAINT fk_room_messages_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE; ALTER TABLE room_messages ADD CONSTRAINT fk_room_messages_code FOREIGN KEY (code_id) REFERENCES codes(id); ALTER TABLE room_messages ADD CONSTRAINT fk_room_messages_user FOREIGN KEY (user_id) REFERENCES users(id); -- Make creator relation explicit ALTER TABLE rooms ADD CONSTRAINT fk_rooms_creator FOREIGN KEY (creator_id) REFERENCES users(id); -- === BACKFILL MIGRATION === -- Populate rooms.creator_id from codes.user_id where available. -- Strategy: use earliest code.date_first for the room that has user_id set. WITH primary_claim AS ( SELECT room_id, user_id, MIN(date_first) as first_date FROM codes WHERE user_id IS NOT NULL AND room_id IS NOT NULL GROUP BY room_id, user_id ) UPDATE rooms r SET creator_id = p.user_id FROM ( SELECT room_id, user_id FROM ( SELECT room_id, user_id, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY first_date ASC) rn FROM ( SELECT room_id, user_id, MIN(date_first) AS first_date FROM codes WHERE user_id IS NOT NULL AND room_id IS NOT NULL GROUP BY room_id, user_id ) t ) s WHERE rn = 1 ) p WHERE r.id = p.room_id AND r.creator_id IS NULL; -- Note: after running this script, verify data and consider manual corrections for rooms without claimed codes. -- End of script