-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Tier 2 · Project spine (shared across Goods/Services/Works)
-- c_projects (with origin + tender linkage), crm_project_onboarding,
-- crm_project_checklist_items, business_leads, bdm_plans, nest_tenders
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS crm_project_checklist_items;
DROP TABLE IF EXISTS crm_project_onboarding;
DROP TABLE IF EXISTS business_leads_follow_ups;
DROP TABLE IF EXISTS business_leads;
DROP TABLE IF EXISTS bdm_plan_objectives;
DROP TABLE IF EXISTS bdm_plans;
DROP TABLE IF EXISTS nest_tenders;
DROP TABLE IF EXISTS c_projects;

CREATE TABLE c_projects (
  c0d                      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  project_no               VARCHAR(60)  NOT NULL,
  project_name             VARCHAR(255) NOT NULL,
  project_type             VARCHAR(20)  NOT NULL, -- goods|services|works
  project_customer_id      INT NOT NULL DEFAULT 0,
  project_customer         VARCHAR(255) NOT NULL DEFAULT '',
  project_tender_reference VARCHAR(120) NOT NULL DEFAULT '',
  -- Origin & tender linkage --------------------------------------------------
  origin                   VARCHAR(20)  NOT NULL DEFAULT 'DIRECT_AWARD', -- TENDER_BDM|TENDER_PUBLIC|DIRECT_AWARD|MIGRATED
  linked_bdm_plan_id       INT NOT NULL DEFAULT 0,
  linked_nest_tender_id    INT NOT NULL DEFAULT 0,
  direct_award_justification TEXT,
  -- Ownership ---------------------------------------------------------------
  project_manager_id       INT NOT NULL DEFAULT 0,
  project_manager_name     VARCHAR(120) NOT NULL DEFAULT '',
  pc_id                    INT NOT NULL DEFAULT 0,
  pc_name                  VARCHAR(120) NOT NULL DEFAULT '',
  -- Dates & value -----------------------------------------------------------
  project_start_date       DATE DEFAULT NULL,
  project_end_date         DATE DEFAULT NULL,
  contract_value           DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency                 VARCHAR(10)  NOT NULL DEFAULT 'TZS',
  default_margin_pct       DECIMAL(6,2) NOT NULL DEFAULT 25.00,
  -- Location ----------------------------------------------------------------
  region                   VARCHAR(80) NOT NULL DEFAULT '',
  district                 VARCHAR(80) NOT NULL DEFAULT '',
  ward                     VARCHAR(80) NOT NULL DEFAULT '',
  -- Gate --------------------------------------------------------------------
  crm_status               VARCHAR(20) NOT NULL DEFAULT 'NOT_STARTED', -- NOT_STARTED|IN_PROGRESS|SUBMITTED|APPROVED|DECLINED
  migrated_flag            TINYINT(1)  NOT NULL DEFAULT 0,
  migrated_note            TEXT,
  -- Lifecycle ---------------------------------------------------------------
  project_status           VARCHAR(20) NOT NULL DEFAULT 'OPEN', -- OPEN|CLOSED|SUSPENDED|CANCELLED
  project_lifecycle_stage  VARCHAR(20) NOT NULL DEFAULT 'NEW',  -- NEW|MOBILISING|EXECUTING|CLOSING|CLOSED
  created_by               INT NOT NULL DEFAULT 0,
  created_on               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by                INT NOT NULL DEFAULT 0,
  edited_on                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted                  TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_project_no (project_no),
  KEY ix_type (project_type),
  KEY ix_customer (project_customer_id),
  KEY ix_origin (origin),
  KEY ix_crm_status (crm_status),
  KEY ix_lifecycle (project_lifecycle_stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CRM Onboarding -----------------------------------------------------------

CREATE TABLE crm_project_onboarding (
  c0d                     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  project_id              INT NOT NULL,
  contract_ref            VARCHAR(120) NOT NULL DEFAULT '',
  contract_value          DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  contract_currency       VARCHAR(10)  NOT NULL DEFAULT 'TZS',
  contract_date           DATE DEFAULT NULL,
  snapshot_taken_on       TIMESTAMP NULL DEFAULT NULL,
  snapshot_data_goods     JSON NULL,
  snapshot_data_services  JSON NULL,
  snapshot_data_works     JSON NULL,
  submitted_at            TIMESTAMP NULL DEFAULT NULL,
  submitted_by            INT NOT NULL DEFAULT 0,
  approved_at             TIMESTAMP NULL DEFAULT NULL,
  approved_by             INT NOT NULL DEFAULT 0,
  created_by              INT NOT NULL DEFAULT 0,
  created_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by               INT NOT NULL DEFAULT 0,
  edited_on               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted                 TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE crm_project_checklist_items (
  c0d             INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  project_id      INT NOT NULL,
  checklist_key   VARCHAR(60) NOT NULL,
  checklist_label VARCHAR(160) NOT NULL,
  is_complete     TINYINT(1) NOT NULL DEFAULT 0,
  completed_on    TIMESTAMP NULL DEFAULT NULL,
  completed_by    INT NOT NULL DEFAULT 0,
  notes           TEXT,
  UNIQUE KEY uq_project_key (project_id, checklist_key),
  KEY ix_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Business Development / CRM upstream -------------------------------------

CREATE TABLE business_leads (
  c0d                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lead_name          VARCHAR(200) NOT NULL,
  customer_id        INT NOT NULL DEFAULT 0,
  customer_name      VARCHAR(160) NOT NULL DEFAULT '',
  lead_source        VARCHAR(60) NOT NULL DEFAULT '',
  project_type       VARCHAR(20) NOT NULL DEFAULT '', -- goods|services|works
  opportunity_value  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency           VARCHAR(10) NOT NULL DEFAULT 'TZS',
  status             VARCHAR(20) NOT NULL DEFAULT 'NEW', -- NEW|QUALIFIED|BIDDING|WON|LOST|ONHOLD
  next_action_date   DATE DEFAULT NULL,
  next_action_note   VARCHAR(255) NOT NULL DEFAULT '',
  linked_project_id  INT NOT NULL DEFAULT 0,
  owner_id           INT NOT NULL DEFAULT 0,
  owner_name         VARCHAR(120) NOT NULL DEFAULT '',
  created_by         INT NOT NULL DEFAULT 0,
  created_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by          INT NOT NULL DEFAULT 0,
  edited_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted            TINYINT(1) NOT NULL DEFAULT 0,
  KEY ix_status (status),
  KEY ix_customer (customer_id),
  KEY ix_owner (owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE business_leads_follow_ups (
  c0d          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lead_id      INT NOT NULL,
  follow_date  DATE NOT NULL,
  follow_note  TEXT,
  created_by   INT NOT NULL DEFAULT 0,
  created_on   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_lead (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bdm_plans (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_name        VARCHAR(200) NOT NULL,
  plan_year        INT NOT NULL,
  target_amount    DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency         VARCHAR(10) NOT NULL DEFAULT 'TZS',
  status           VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
  owner_id         INT NOT NULL DEFAULT 0,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by        INT NOT NULL DEFAULT 0,
  edited_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted          TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bdm_plan_objectives (
  c0d          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_id      INT NOT NULL,
  objective    VARCHAR(255) NOT NULL,
  target_value DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  KEY ix_plan (plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE nest_tenders (
  c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ocid              VARCHAR(120) NOT NULL,
  tender_title      VARCHAR(400) NOT NULL,
  procuring_entity  VARCHAR(255) NOT NULL DEFAULT '',
  tender_value      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency          VARCHAR(10) NOT NULL DEFAULT 'TZS',
  category          VARCHAR(60) NOT NULL DEFAULT '',
  region            VARCHAR(80) NOT NULL DEFAULT '',
  closing_date      DATE DEFAULT NULL,
  tender_url        VARCHAR(500) NOT NULL DEFAULT '',
  status            VARCHAR(20) NOT NULL DEFAULT 'OPEN', -- OPEN|SHORTLISTED|SUBMITTED|WON|LOST|IGNORED
  synced_on         TIMESTAMP NULL DEFAULT NULL,
  linked_project_id INT NOT NULL DEFAULT 0,
  created_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ocid (ocid),
  KEY ix_status (status),
  KEY ix_closing (closing_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
