scripts/postgres_schema.sql

-- Supabase Postgres Schema Migration
-- Generated based on Drizzle SQLite schema
-- STRICT RLS VERSION (No legacy access)

-- 1. Clients Table
CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  company TEXT,
  notes TEXT,
  user_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Projects Table
CREATE TABLE IF NOT EXISTS projects (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title TEXT NOT NULL,
  description TEXT,
  client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
  status TEXT NOT NULL DEFAULT 'briefing',
  priority TEXT NOT NULL DEFAULT 'medium',
  budget DOUBLE PRECISION,
  deadline TEXT,
  drive_url TEXT,
  plan_url TEXT,
  user_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tasks Table
CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT FALSE,
  due_date TEXT,
  user_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. Time Entries Table
CREATE TABLE IF NOT EXISTS time_entries (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  description TEXT,
  hours DOUBLE PRECISION NOT NULL,
  date TEXT NOT NULL,
  user_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Briefs Table
CREATE TABLE IF NOT EXISTS briefs (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title TEXT NOT NULL,
  category TEXT,
  content TEXT NOT NULL,
  user_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Enable RLS on all tables
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE time_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE briefs ENABLE ROW LEVEL SECURITY;

-- Create STRICT RLS Policies (Explicit SELECT, INSERT, UPDATE, DELETE)
-- Normal users should only access rows where user_id = auth.uid()

-- 1. Clients Policies
-- إضافة سياسة لتصفية البيانات الخاصة بكل مستخدم
CREATE POLICY select_policy
ON clients
FOR SELECT
USING (auth.uid() = user_id);


-- إضافة سياسة لإدخال البيانات
CREATE POLICY insert_policy
ON clients
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- سياسة لتحديث البيانات
CREATE POLICY update_policy
ON clients
FOR UPDATE
USING (auth.uid() = user_id);

-- سياسة لحذف البيانات
CREATE POLICY delete_policy
ON clients
FOR DELETE
USING (auth.uid() = user_id);



-- 2. Projects Policies
-- إضافة سياسة لتصفية البيانات الخاصة بكل مستخدم
CREATE POLICY select_policy
ON projects
FOR SELECT
USING (auth.uid() = user_id);


-- إضافة سياسة لإدخال البيانات
CREATE POLICY insert_policy
ON projects
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- سياسة لتحديث البيانات
CREATE POLICY update_policy
ON projects
FOR UPDATE
USING (auth.uid() = user_id);

-- سياسة لحذف البيانات
CREATE POLICY delete_policy
ON projects
FOR DELETE
USING (auth.uid() = user_id);



-- 3. Tasks Policies
-- إضافة سياسة لتصفية البيانات الخاصة بكل مستخدم
CREATE POLICY select_policy
ON tasks
FOR SELECT
USING (auth.uid() = user_id);


-- إضافة سياسة لإدخال البيانات
CREATE POLICY insert_policy
ON tasks
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- سياسة لتحديث البيانات
CREATE POLICY update_policy
ON tasks
FOR UPDATE
USING (auth.uid() = user_id);

-- سياسة لحذف البيانات
CREATE POLICY delete_policy
ON tasks
FOR DELETE
USING (auth.uid() = user_id);



-- 4. Time Entries Policies
-- إضافة سياسة لتصفية البيانات الخاصة بكل مستخدم
CREATE POLICY select_policy
ON time_entries
FOR SELECT
USING (auth.uid() = user_id);


-- إضافة سياسة لإدخال البيانات
CREATE POLICY insert_policy
ON time_entries
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- سياسة لتحديث البيانات
CREATE POLICY update_policy
ON time_entries
FOR UPDATE
USING (auth.uid() = user_id);

-- سياسة لحذف البيانات
CREATE POLICY delete_policy
ON time_entries
FOR DELETE
USING (auth.uid() = user_id);



-- 5. Briefs Policies
-- إضافة سياسة لتصفية البيانات الخاصة بكل مستخدم
CREATE POLICY select_policy
ON briefs
FOR SELECT
USING (auth.uid() = user_id);


-- إضافة سياسة لإدخال البيانات
CREATE POLICY insert_policy
ON briefs
FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- سياسة لتحديث البيانات
CREATE POLICY update_policy
ON briefs
FOR UPDATE
USING (auth.uid() = user_id);

-- سياسة لحذف البيانات
CREATE POLICY delete_policy
ON briefs
FOR DELETE
USING (auth.uid() = user_id);