db/schema.sql

-- PostgreSQL schema for KIllaDesign data model
-- Safe re-runnable DDL

-- Recommended: run inside a dedicated schema
CREATE SCHEMA IF NOT EXISTS kd;
SET search_path TO kd, public;

-- Domain definitions (optionally used for clarity)
CREATE DOMAIN IF NOT EXISTS money_aed AS NUMERIC(16,2)
  CHECK (VALUE >= 0);

-- Lookup tables (optional, minimal to keep example concise)
CREATE TABLE IF NOT EXISTS project_types (
  type TEXT PRIMARY KEY
);
INSERT INTO project_types(type) VALUES
  ('Mixed-Use'), ('Hospitality'), ('Office'), ('Residential'), ('Cultural')
ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS work_categories (
  category TEXT PRIMARY KEY
);
INSERT INTO work_categories(category) VALUES
  ('Architecture'), ('Project Management'), ('Subcontractor')
ON CONFLICT DO NOTHING;

-- Core tables
CREATE TABLE IF NOT EXISTS projects (
  project_id TEXT PRIMARY KEY,
  project_name TEXT NOT NULL,
  client_name TEXT NOT NULL,
  contract_value_aed money_aed NOT NULL,
  planned_cost_aed money_aed NOT NULL,
  project_start_date DATE NOT NULL,
  project_end_date DATE,
  project_type TEXT REFERENCES project_types(type),
  project_status TEXT NOT NULL,
  current_phase TEXT
);

CREATE TABLE IF NOT EXISTS employees (
  employee_id TEXT PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department TEXT,
  role TEXT,
  hourly_rate_aed money_aed NOT NULL,
  employment_type TEXT,
  start_date DATE,
  cost_category TEXT REFERENCES work_categories(category)
);

CREATE TABLE IF NOT EXISTS milestones (
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  milestone_name TEXT NOT NULL,
  milestone_order INT NOT NULL,
  planned_date DATE,
  actual_date DATE,
  status TEXT,
  completion_percentage INT CHECK (completion_percentage BETWEEN 0 AND 100),
  PRIMARY KEY (project_id, milestone_order)
);

CREATE TABLE IF NOT EXISTS invoices (
  invoice_id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  invoice_date DATE NOT NULL,
  invoice_amount_aed money_aed NOT NULL,
  due_date DATE,
  payment_date DATE,
  payment_status TEXT,
  days_outstanding INT,
  milestone_reference TEXT
);

CREATE TABLE IF NOT EXISTS subcontractors (
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  subcontractor_name TEXT NOT NULL,
  service_type TEXT NOT NULL,
  contract_amount_aed money_aed NOT NULL,
  amount_invoiced_aed money_aed NOT NULL,
  payment_status TEXT,
  work_category TEXT REFERENCES work_categories(category),
  PRIMARY KEY (project_id, subcontractor_name, service_type)
);

CREATE TABLE IF NOT EXISTS timesheets (
  record_id TEXT PRIMARY KEY,
  date DATE NOT NULL,
  employee_id TEXT NOT NULL REFERENCES employees(employee_id) ON DELETE RESTRICT,
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  hours_worked NUMERIC(6,2) NOT NULL CHECK (hours_worked >= 0),
  work_category TEXT REFERENCES work_categories(category),
  task_description TEXT,
  billable_hours NUMERIC(6,2) CHECK (billable_hours >= 0)
);

CREATE TABLE IF NOT EXISTS milestone_budgets (
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  milestone_name TEXT NOT NULL,
  milestone_order INT NOT NULL,
  planned_date DATE,
  actual_date DATE,
  status TEXT,
  budgeted_hours NUMERIC(10,2),
  budgeted_cost_aed money_aed,
  actual_hours NUMERIC(10,2),
  actual_cost_aed money_aed,
  cumulative_budgeted_hours NUMERIC(10,2),
  cumulative_budgeted_cost_aed money_aed,
  cumulative_actual_hours NUMERIC(10,2),
  cumulative_actual_cost_aed money_aed,
  hours_variance NUMERIC(10,2),
  cost_variance_aed money_aed,
  schedule_variance_days INT,
  budget_utilization_percent NUMERIC(5,2),
  architecture_hours NUMERIC(10,2),
  architecture_cost_aed money_aed,
  pm_hours NUMERIC(10,2),
  pm_cost_aed money_aed,
  subcontractor_hours NUMERIC(10,2),
  subcontractor_cost_aed money_aed,
  notes TEXT,
  PRIMARY KEY (project_id, milestone_order)
);

CREATE TABLE IF NOT EXISTS staff_allocation (
  project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
  milestone_id TEXT,
  milestone_name TEXT,
  employee_id TEXT NOT NULL REFERENCES employees(employee_id) ON DELETE RESTRICT,
  employee_name TEXT,
  role TEXT,
  category TEXT,
  hours_allocated NUMERIC(10,2),
  hours_worked NUMERIC(10,2),
  hourly_rate_aed money_aed,
  cost_aed money_aed,
  utilization_percent NUMERIC(6,2),
  skill_match_score NUMERIC(6,2),
  availability_status TEXT,
  start_date DATE,
  end_date DATE,
  performance_rating TEXT,
  notes TEXT,
  PRIMARY KEY (project_id, milestone_name, employee_id)
);