CREATE TABLE IF NOT EXISTS projects (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), award_id uuid REFERENCES awards(id), name varchar(255) NOT NULL, client varchar(255), vendor_id uuid REFERENCES vendors(id), project_value numeric(18,2) NOT NULL DEFAULT 0, start_date date, end_date date, status varchar(30) NOT NULL DEFAULT 'planned', pm_id uuid REFERENCES users(id), progress_percentage numeric(5,2) NOT NULL DEFAULT 0 CHECK(progress_percentage BETWEEN 0 AND 100), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS tasks (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE, title varchar(255) NOT NULL, description text, owner_id uuid REFERENCES users(id), start_date date, due_date date, priority varchar(30) NOT NULL DEFAULT 'medium', status varchar(30) NOT NULL DEFAULT 'pending', progress_percentage numeric(5,2) NOT NULL DEFAULT 0 CHECK(progress_percentage BETWEEN 0 AND 100), parent_task_id uuid REFERENCES tasks(id), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS task_comments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), task_id uuid NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, commented_by uuid REFERENCES users(id), parent_comment_id uuid REFERENCES task_comments(id), comment_text text NOT NULL, commented_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS task_attachments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), task_id uuid NOT NULL REFERENCES tasks(id) ON DELETE CASCADE, original_name text NOT NULL, stored_name text NOT NULL, storage_path text NOT NULL, mime_type varchar(255), size_bytes bigint NOT NULL, uploaded_by uuid REFERENCES users(id), uploaded_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS budgets (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), project_id uuid UNIQUE NOT NULL REFERENCES projects(id) ON DELETE CASCADE, total_allocation numeric(18,2) NOT NULL DEFAULT 0, utilized numeric(18,2) NOT NULL DEFAULT 0, remaining numeric(18,2) NOT NULL DEFAULT 0, threshold_warning numeric(5,2) NOT NULL DEFAULT 80, threshold_critical numeric(5,2) NOT NULL DEFAULT 100, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS budget_lines (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), budget_id uuid NOT NULL REFERENCES budgets(id) ON DELETE CASCADE, boq_line_id uuid REFERENCES boq_lines(id), allocated_amount numeric(18,2) NOT NULL DEFAULT 0, utilized_amount numeric(18,2) NOT NULL DEFAULT 0, remaining_amount numeric(18,2) NOT NULL DEFAULT 0, UNIQUE(budget_id,boq_line_id));
CREATE TABLE IF NOT EXISTS invoices (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), vendor_id uuid NOT NULL REFERENCES vendors(id), project_id uuid NOT NULL REFERENCES projects(id), invoice_number varchar(120) NOT NULL, invoice_date date NOT NULL, amount numeric(18,2) NOT NULL CHECK(amount>=0), tax numeric(18,2) NOT NULL DEFAULT 0, total_amount numeric(18,2) NOT NULL DEFAULT 0, status varchar(30) NOT NULL DEFAULT 'draft', rejection_reason text, created_by uuid REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE(project_id,invoice_number));
CREATE TABLE IF NOT EXISTS invoice_attachments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid NOT NULL REFERENCES invoices(id) ON DELETE CASCADE, original_name text NOT NULL, stored_name text NOT NULL, storage_path text NOT NULL, mime_type varchar(255), size_bytes bigint NOT NULL, uploaded_by uuid REFERENCES users(id), uploaded_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS payments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid UNIQUE NOT NULL REFERENCES invoices(id), payment_date date NOT NULL, transaction_reference varchar(160) NOT NULL, amount numeric(18,2) NOT NULL CHECK(amount>=0), notes text, proof_key text, recorded_by uuid REFERENCES users(id), recorded_at timestamptz NOT NULL DEFAULT now());