-- Final Supabase schema and migration
-- Generated: 2025-12-09T19:57:19.113Z
-- Intended for Supabase (auth schema present). 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 ===
-- 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 ===
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_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_logs_created_at ON logs(created_at);

-- === FOREIGN KEYS ===
ALTER TABLE users
  ADD CONSTRAINT fk_users_auth FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE;

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

-- === RLS / POLICIES ===
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;

-- Example policies (adapt per project security model)
-- Users: view/insert/update own profile
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);

-- Logs: 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');

-- === 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