BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TYPE project_status AS ENUM ('DRAFT','UNDER_REVIEW','PENDING_VERIFICATION','VERIFIED','PUBLISHED','ACTIVE','PAUSED','COMPLETED','ARCHIVED');
CREATE TYPE kyc_status AS ENUM ('PENDING','UNDER_REVIEW','APPROVED','REJECTED','REQUIRES_MORE_INFORMATION','EXPIRED');
CREATE TYPE investment_request_status AS ENUM ('DRAFT','SUBMITTED','UNDER_REVIEW','KYC_REQUIRED','COMPLIANCE_REVIEW','APPROVED','REJECTED','CANCELLED','COMPLETED');
CREATE TYPE payment_status AS ENUM ('PENDING','PROCESSING','COMPLETED','FAILED','REFUNDED','CANCELLED','RECONCILED');

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE,
  phone text UNIQUE,
  full_name text,
  password_hash text,
  email_verified_at timestamptz,
  phone_verified_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE permissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  description text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE user_roles (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
  role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  ip inet,
  user_agent text,
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);

CREATE TABLE devices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_name text,
  fingerprint_hash text,
  last_seen_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE kyc_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  status kyc_status NOT NULL DEFAULT 'PENDING',
  full_name text,
  date_of_birth date,
  nationality text,
  id_number_ciphertext text,
  address_ciphertext text,
  source_of_funds_ciphertext text,
  reviewed_by uuid REFERENCES users(id),
  reviewed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE kyc_documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kyc_profile_id uuid NOT NULL REFERENCES kyc_profiles(id) ON DELETE CASCADE,
  object_key text NOT NULL,
  document_type text NOT NULL,
  content_hash text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text NOT NULL UNIQUE,
  category text NOT NULL,
  status project_status NOT NULL DEFAULT 'DRAFT',
  description text,
  approximate_location text,
  exact_location_ciphertext text,
  verification_status text NOT NULL DEFAULT 'UNVERIFIED',
  verified_by uuid REFERENCES users(id),
  verified_at timestamptz,
  is_demo boolean NOT NULL DEFAULT false,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_projects_status_category ON projects(status, category);

CREATE TABLE project_documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name text NOT NULL,
  object_key text NOT NULL,
  version integer NOT NULL DEFAULT 1 CHECK (version > 0),
  confidentiality text NOT NULL DEFAULT 'PRIVATE',
  status text NOT NULL DEFAULT 'DRAFT',
  content_hash text NOT NULL,
  expires_at timestamptz,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE project_updates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text NOT NULL,
  is_public boolean NOT NULL DEFAULT false,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE project_metrics (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  metric_key text NOT NULL,
  value numeric NOT NULL,
  unit text NOT NULL,
  measured_on date NOT NULL,
  source text NOT NULL,
  evidence_document_id uuid REFERENCES project_documents(id),
  entered_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_project_metrics_lookup ON project_metrics(project_id, metric_key, measured_on DESC);

CREATE TABLE farms (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE farm_units (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  farm_id uuid NOT NULL REFERENCES farms(id) ON DELETE CASCADE,
  unit_type text NOT NULL,
  name text NOT NULL,
  capacity numeric,
  capacity_unit text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE poultry_batches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  farm_unit_id uuid NOT NULL REFERENCES farm_units(id) ON DELETE CASCADE,
  batch_code text NOT NULL,
  bird_type text NOT NULL,
  opening_count integer NOT NULL CHECK (opening_count >= 0),
  started_on date NOT NULL,
  closed_on date,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(farm_unit_id, batch_code)
);

CREATE TABLE egg_production (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  poultry_batch_id uuid NOT NULL REFERENCES poultry_batches(id) ON DELETE CASCADE,
  production_date date NOT NULL,
  opening_poultry integer NOT NULL CHECK (opening_poultry >= 0),
  additions integer NOT NULL DEFAULT 0 CHECK (additions >= 0),
  mortality integer NOT NULL DEFAULT 0 CHECK (mortality >= 0),
  closing_poultry integer NOT NULL CHECK (closing_poultry >= 0),
  eggs_total integer NOT NULL DEFAULT 0 CHECK (eggs_total >= 0),
  good_eggs integer NOT NULL DEFAULT 0 CHECK (good_eggs >= 0),
  broken_eggs integer NOT NULL DEFAULT 0 CHECK (broken_eggs >= 0),
  feed_consumption numeric,
  feed_unit text,
  water_consumption numeric,
  water_unit text,
  medication text,
  notes text,
  employee_id uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT ck_closing_poultry CHECK (closing_poultry = opening_poultry + additions - mortality),
  CONSTRAINT ck_egg_breakdown CHECK (good_eggs + broken_eggs <= eggs_total),
  UNIQUE(poultry_batch_id, production_date)
);

CREATE TABLE inventory_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  sku text NOT NULL,
  name text NOT NULL,
  unit text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(project_id, sku)
);

CREATE TABLE inventory_transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id uuid NOT NULL REFERENCES inventory_items(id) ON DELETE RESTRICT,
  transaction_type text NOT NULL,
  quantity numeric NOT NULL CHECK (quantity > 0),
  unit_cost numeric CHECK (unit_cost IS NULL OR unit_cost >= 0),
  occurred_at timestamptz NOT NULL,
  source text NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_inventory_tx_item_time ON inventory_transactions(item_id, occurred_at DESC);

CREATE TABLE suppliers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name text NOT NULL,
  contact_data_ciphertext text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE customers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name text NOT NULL,
  contact_data_ciphertext text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE sales (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  customer_id uuid REFERENCES customers(id),
  amount numeric NOT NULL CHECK (amount >= 0),
  currency char(3) NOT NULL DEFAULT 'IQD',
  occurred_on date NOT NULL,
  source text NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE expenses (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  supplier_id uuid REFERENCES suppliers(id),
  amount numeric NOT NULL CHECK (amount >= 0),
  currency char(3) NOT NULL DEFAULT 'IQD',
  category text NOT NULL,
  occurred_on date NOT NULL,
  source text NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE investment_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE RESTRICT,
  status investment_request_status NOT NULL DEFAULT 'DRAFT',
  requested_amount numeric CHECK (requested_amount IS NULL OR requested_amount > 0),
  currency char(3) DEFAULT 'IQD',
  submitted_at timestamptz,
  reviewed_by uuid REFERENCES users(id),
  reviewed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_investment_requests_user_status ON investment_requests(user_id, status);

CREATE TABLE contracts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  investment_request_id uuid REFERENCES investment_requests(id) ON DELETE RESTRICT,
  status text NOT NULL DEFAULT 'DRAFT',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE contract_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  contract_id uuid NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
  version integer NOT NULL CHECK (version > 0),
  object_key text NOT NULL,
  content_hash text NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(contract_id, version)
);

CREATE TABLE payment_intents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  investment_request_id uuid REFERENCES investment_requests(id) ON DELETE RESTRICT,
  contract_id uuid REFERENCES contracts(id) ON DELETE RESTRICT,
  amount numeric NOT NULL CHECK (amount > 0),
  currency char(3) NOT NULL,
  provider text NOT NULL,
  external_reference text,
  status payment_status NOT NULL DEFAULT 'PENDING',
  idempotency_key text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE payment_transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  payment_intent_id uuid NOT NULL REFERENCES payment_intents(id) ON DELETE RESTRICT,
  provider_transaction_id text,
  status payment_status NOT NULL,
  amount numeric NOT NULL CHECK (amount > 0),
  currency char(3) NOT NULL,
  occurred_at timestamptz NOT NULL,
  raw_reference text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type text NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  read_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE support_tickets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  status text NOT NULL DEFAULT 'OPEN',
  priority text NOT NULL DEFAULT 'NORMAL',
  subject text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE audit_logs (
  id bigserial PRIMARY KEY,
  actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id text,
  ip inet,
  device text,
  old_value jsonb,
  new_value jsonb,
  request_id text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id, created_at DESC);
CREATE INDEX idx_audit_actor_time ON audit_logs(actor_user_id, created_at DESC);

CREATE TABLE risk_flags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  flag_type text NOT NULL,
  severity text NOT NULL,
  status text NOT NULL DEFAULT 'OPEN',
  details jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  resolved_at timestamptz
);

CREATE TABLE compliance_reviews (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  subject_type text NOT NULL,
  subject_id text NOT NULL,
  status text NOT NULL,
  notes text,
  reviewed_by uuid REFERENCES users(id),
  reviewed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE system_settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  updated_by uuid REFERENCES users(id),
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO system_settings(key, value) VALUES
  ('LIVE_INVESTMENT', 'false'::jsonb),
  ('COLLECT_FUNDS', 'false'::jsonb),
  ('LIVE_PAYMENTS', 'false'::jsonb);

COMMIT;