src/db/localSQLcreate.sql

-- Local DB schema for development
-- This script is intended for running against a local Postgres instance (psql, Docker, or Supabase local).
-- Differences from supabaseSQLcreate.sql:
--  * Does NOT reference auth.users schema (no Supabase auth dependency)
--  * Does NOT enable RLS or policies (local dev convenience)
--  * Keeps indexes and FK constraints for referential integrity

-- Drop existing tables if they exist (safe teardown for re-creating DB)
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;

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Rooms table
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 table (local development: generate id locally)
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 table
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,
  room_id UUID,
  pin_hash VARCHAR,
  session_hash VARCHAR,
  date_first TIMESTAMP,
  date_last TIMESTAMP,
  used_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Room messages table
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 table (local)
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
);

-- Create 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);

-- Add foreign key constraints
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);