-- TECTOPI VEHICLE canonical schema (MySQL 8.0+ / MariaDB with JSON support)
-- Import into a NEW staging database first. Review SQL against your hosting version.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('FOUNDER','OPERATIONS_LEAD','PROJECT_MANAGER','BUSINESS_DEVELOPMENT','FINANCE','TEAM_MEMBER','ADMIN') NOT NULL DEFAULT 'TEAM_MEMBER',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(220) NOT NULL,
  organisation VARCHAR(220) NULL,
  description TEXT NULL,
  problem_need TEXT NULL,
  strategic_engine ENUM('HUMAN_CAPITAL','DIGITAL_TRANSFORMATION','CREATIVE_ECONOMY','SUSTAINABILITY') NOT NULL DEFAULT 'HUMAN_CAPITAL',
  current_stage ENUM('PREPARE','PLANT','CULTIVATE','HARVEST') NOT NULL DEFAULT 'PREPARE',
  status ENUM('OPEN','ON_HOLD','WON','LOST','CLOSED') NOT NULL DEFAULT 'OPEN',
  qualification_status ENUM('UNQUALIFIED','QUALIFIED','DISQUALIFIED') NOT NULL DEFAULT 'UNQUALIFIED',
  accountable_owner_id BIGINT UNSIGNED NULL,
  next_action VARCHAR(255) NULL,
  next_action_due DATE NULL,
  deadline DATE NULL,
  decision_maker VARCHAR(190) NULL,
  procurement_route VARCHAR(190) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  estimated_value DECIMAL(18,2) NULL,
  submitted_bid_value DECIMAL(18,2) NULL,
  signed_contract_value DECIMAL(18,2) NULL,
  invoiced_revenue DECIMAL(18,2) NOT NULL DEFAULT 0,
  cash_collected DECIMAL(18,2) NOT NULL DEFAULT 0,
  pursuit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  approved_budget DECIMAL(18,2) NULL,
  committed_spend DECIMAL(18,2) NOT NULL DEFAULT 0,
  forecast_spend DECIMAL(18,2) NOT NULL DEFAULT 0,
  budget_warning_percent DECIMAL(5,2) NOT NULL DEFAULT 80.00,
  delivered_at DATETIME NULL,
  invoice_required TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_opportunity_owner FOREIGN KEY (accountable_owner_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_opportunity_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_opportunity_stage_status (current_stage, status),
  INDEX idx_opportunity_qualification (qualification_status, status),
  INDEX idx_opportunity_owner (accountable_owner_id),
  INDEX idx_opportunity_deadline (deadline),
  INDEX idx_opportunity_next_action (next_action_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NULL,
  status ENUM('PLANNED','ACTIVE','AT_RISK','ON_HOLD','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PLANNED',
  project_manager_id BIGINT UNSIGNED NULL,
  start_date DATE NULL,
  due_date DATE NULL,
  approved_budget DECIMAL(18,2) NULL,
  committed_spend DECIMAL(18,2) NOT NULL DEFAULT 0,
  forecast_spend DECIMAL(18,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_project_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE SET NULL,
  CONSTRAINT fk_project_manager FOREIGN KEY (project_manager_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_project_status (status),
  INDEX idx_project_due (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NULL,
  project_id BIGINT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NULL,
  owner_user_id BIGINT UNSIGNED NOT NULL,
  assigned_by_user_id BIGINT UNSIGNED NULL,
  status ENUM('OPEN','IN_PROGRESS','BLOCKED','SUBMITTED','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
  quality_status ENUM('NOT_REVIEWED','PENDING_REVIEW','ACCEPTED','REWORK','REJECTED') NOT NULL DEFAULT 'NOT_REVIEWED',
  acceptance_criteria TEXT NULL,
  reviewer_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  review_note TEXT NULL,
  complexity ENUM('LOW','MEDIUM','HIGH','VERY_HIGH') NOT NULL DEFAULT 'MEDIUM',
  due_date DATE NULL,
  agreed_due_date DATE NULL,
  grace_until DATETIME NULL,
  blocked_reason TEXT NULL,
  started_at DATETIME NULL,
  submitted_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_owner FOREIGN KEY (owner_user_id) REFERENCES users(id),
  CONSTRAINT fk_task_assigner FOREIGN KEY (assigned_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_reviewer FOREIGN KEY (reviewer_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_task_owner_status (owner_user_id, status),
  INDEX idx_task_due_status (due_date, status),
  INDEX idx_task_quality (quality_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  uploaded_by_user_id BIGINT UNSIGNED NULL,
  document_type ENUM('GENERAL','EVIDENCE','PROPOSAL','CONTRACT','INVOICE_REFERENCE','PAYMENT_RECEIPT','DELIVERABLE','APPROVAL') NOT NULL DEFAULT 'GENERAL',
  title VARCHAR(220) NOT NULL,
  storage_key VARCHAR(255) NULL,
  external_reference VARCHAR(500) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_doc_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_doc_uploader FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_doc_type (opportunity_id, document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_risks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NULL,
  severity ENUM('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'MEDIUM',
  status ENUM('OPEN','IN_PROGRESS','MITIGATED','CLOSED') NOT NULL DEFAULT 'OPEN',
  owner_user_id BIGINT UNSIGNED NULL,
  target_resolution_date DATE NULL,
  mitigation TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_risk_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_risk_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_risk_status_severity (status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_approvals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  requested_by_user_id BIGINT UNSIGNED NULL,
  approver_user_id BIGINT UNSIGNED NULL,
  approval_type VARCHAR(100) NOT NULL,
  request_summary TEXT NOT NULL,
  status ENUM('PENDING','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  sla_due_at DATETIME NULL,
  decision_note TEXT NULL,
  decided_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_requester FOREIGN KEY (requested_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_approval_approver FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_approval_status_sla (status, sla_due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(100) NOT NULL,
  summary VARCHAR(500) NOT NULL,
  details JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_history_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_history_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_history_opportunity_date (opportunity_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_quality_reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT UNSIGNED NOT NULL,
  reviewer_user_id BIGINT UNSIGNED NOT NULL,
  outcome ENUM('ACCEPTED','REWORK','REJECTED') NOT NULL,
  review_note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_review_task FOREIGN KEY (task_id) REFERENCES opportunity_tasks(id) ON DELETE CASCADE,
  CONSTRAINT fk_review_reviewer FOREIGN KEY (reviewer_user_id) REFERENCES users(id),
  INDEX idx_review_task_date (task_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cash_receipts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  received_at DATE NOT NULL,
  receipt_reference VARCHAR(190) NOT NULL,
  reconciliation_status ENUM('PENDING','CONFIRMED','REJECTED') NOT NULL DEFAULT 'PENDING',
  confirmed_by_user_id BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_receipt_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_receipt_confirmer FOREIGN KEY (confirmed_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_receipt_reference (receipt_reference),
  INDEX idx_receipt_confirmed_date (reconciliation_status, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(100) NOT NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  ip_hash CHAR(64) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_activity_entity (entity_type, entity_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;