src/db/supabaseSQLcreate.sql

-- 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 (ensure gen_random_uuid is available)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Rooms table (created first because other tables reference it)
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
-- id MUST match auth.users.id (no default) to keep auth as single source of truth
CREATE TABLE users (
  id UUID PRIMARY KEY,
  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 without FK references to avoid dependency ordering issues)
CREATE TABLE codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(5) UNIQUE NOT NULL,
  linked_to UUID, -- will add self-FK constraint below
  used INTEGER,
  user_id UUID,   -- will add FK constraint below
  room_id UUID,   -- will add FK constraint below
  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 without FK references to avoid ordering issues)
CREATE TABLE room_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id UUID,   -- FK added below
  code_id UUID,   -- FK added below
  user_id UUID,   -- FK added below
  content TEXT NOT NULL,
  image_url VARCHAR,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Logs table
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_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_logs_created_at ON logs(created_at);

-- Add foreign key constraints after all tables exist (avoids "relation does not exist" errors)
-- Ensure profile id references auth.users(id) for consistency
ALTER TABLE users
  ADD CONSTRAINT fk_users_auth FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;

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

-- Make creator relation explicit
ALTER TABLE rooms
  ADD CONSTRAINT fk_rooms_creator FOREIGN KEY (creator_id) REFERENCES users(id);
-- room_messages foreign keys
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);

-- Logs (no FKs)

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE room_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE logs ENABLE ROW LEVEL SECURITY;

-- RLS policies for users
DROP POLICY IF EXISTS "Users can view own profile" ON users;
CREATE POLICY "Users can view own profile" ON users
  FOR SELECT USING (auth.uid()::text = id::text);

DROP POLICY IF EXISTS "Users can insert own profile" ON users;
CREATE POLICY "Users can insert own profile" ON users
  FOR INSERT WITH CHECK (auth.role() = 'service_role' OR auth.uid()::text = id::text);

DROP POLICY IF EXISTS "Users can update own profile" ON users;
CREATE POLICY "Users can update own profile" ON users
  FOR UPDATE USING (auth.uid()::text = id::text) WITH CHECK (auth.uid()::text = id::text);

DROP POLICY IF EXISTS "Users can delete own profile" ON users;
CREATE POLICY "Users can delete own profile" ON users
  FOR DELETE USING (auth.uid()::text = id::text);

-- Logs: allow service role only
DROP POLICY IF EXISTS "Logs service role only" ON logs;
CREATE POLICY "Logs service role only" ON logs
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- Note: service role bypasses RLS; use service role for migrations and admin operations.
-- Additional policies for other tables should reference auth.uid() similarly where needed.