schema.sql

CREATE TABLE roles (
  role_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  role_code TEXT UNIQUE NOT NULL,
  role_name TEXT NOT NULL,
  role_level INT NOT NULL
);

CREATE TABLE credit_hubs (
  hub_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  hub_code TEXT UNIQUE NOT NULL,
  hub_name TEXT NOT NULL,
  city TEXT NOT NULL,
  state TEXT NOT NULL
);

CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_id TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  role_id UUID REFERENCES roles(role_id),
  hub_id UUID REFERENCES credit_hubs(hub_id),
  branch_name TEXT,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE cases (
  case_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kurie_no TEXT NOT NULL,
  draw_no INT NOT NULL,
  loyalty_id TEXT NOT NULL,
  chittal_no TEXT NOT NULL,
  chittal TEXT NOT NULL,
  prize_money NUMERIC(14,2) NOT NULL,
  future_liability NUMERIC(14,2) NOT NULL,
  remaining_liability NUMERIC(14,2) NOT NULL,
  bid_loss NUMERIC(14,2),
  branch_name TEXT NOT NULL,
  current_hub_id UUID REFERENCES credit_hubs(hub_id),
  pending_days INT DEFAULT 0,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE security_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  formula_config JSONB NOT NULL,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE parties (
  party_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  party_type TEXT NOT NULL,
  loyalty_id TEXT,
  temp_id TEXT,
  full_name TEXT NOT NULL,
  gross_monthly_income NUMERIC(14,2)
);

CREATE TABLE case_securities (
  security_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  security_type_id UUID REFERENCES security_types(id),
  party_id UUID REFERENCES parties(party_id),
  holder_type TEXT NOT NULL,
  accepted_value NUMERIC(14,2) NOT NULL,
  input_params JSONB NOT NULL
);

CREATE TABLE document_master (
  doc_code TEXT PRIMARY KEY,
  doc_title TEXT NOT NULL,
  category TEXT NOT NULL,
  upload_method TEXT NOT NULL
);

CREATE TABLE document_security_mapping (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  doc_code TEXT REFERENCES document_master(doc_code),
  security_type_id UUID REFERENCES security_types(id),
  applicable_to TEXT NOT NULL,
  is_mandatory BOOLEAN DEFAULT TRUE
);

CREATE TABLE case_documents (
  doc_instance_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  party_id UUID REFERENCES parties(party_id),
  doc_code TEXT REFERENCES document_master(doc_code),
  status TEXT NOT NULL,
  file_url TEXT,
  uploaded_at TIMESTAMPTZ,
  verified_by UUID REFERENCES users(user_id)
);

CREATE TABLE dispatch_records (
  dispatch_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID UNIQUE REFERENCES cases(case_id),
  hub_id UUID REFERENCES credit_hubs(hub_id),
  courier_name TEXT NOT NULL,
  awb_number TEXT NOT NULL,
  dispatch_date TIMESTAMPTZ NOT NULL,
  received_date TIMESTAMPTZ
);

CREATE TABLE field_investigations (
  fi_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  assigned_to UUID REFERENCES users(user_id),
  questionnaire_responses JSONB,
  status TEXT NOT NULL
);

CREATE TABLE fi_uploads (
  upload_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  fi_id UUID REFERENCES field_investigations(fi_id),
  upload_type TEXT NOT NULL,
  file_url TEXT NOT NULL,
  gps_latitude NUMERIC(10,7),
  gps_longitude NUMERIC(10,7)
);

CREATE TABLE approvals (
  approval_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  approval_level TEXT NOT NULL,
  approver_id UUID REFERENCES users(user_id),
  decision TEXT NOT NULL,
  remarks TEXT,
  decided_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE case_workflow_log (
  log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  from_status TEXT,
  to_status TEXT NOT NULL,
  action TEXT NOT NULL,
  actioned_by UUID REFERENCES users(user_id),
  remarks TEXT,
  actioned_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE audit_logs (
  log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  timestamp TIMESTAMPTZ DEFAULT now(),
  user_id UUID REFERENCES users(user_id),
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id UUID NOT NULL,
  old_value JSONB,
  new_value JSONB,
  ip_address INET
);

CREATE TABLE sla_tracking (
  sla_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  role_code TEXT NOT NULL,
  started_at TIMESTAMPTZ NOT NULL,
  sla_deadline TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ,
  status TEXT NOT NULL
);

CREATE TABLE notifications (
  notif_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id UUID REFERENCES cases(case_id),
  recipient_id UUID REFERENCES users(user_id),
  type TEXT NOT NULL,
  channel TEXT NOT NULL,
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  sent_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_cases_status_hub ON cases(status, current_hub_id);
CREATE INDEX idx_cases_draw_pending ON cases(created_at, pending_days);
CREATE INDEX idx_case_documents_case_doc ON case_documents(case_id, doc_code);
CREATE INDEX idx_audit_entity ON audit_logs(entity_id, entity_type);
CREATE INDEX idx_sla_status_deadline ON sla_tracking(status, sla_deadline);
CREATE INDEX idx_notifications_recipient ON notifications(recipient_id, is_read);