-- =====================================================================
-- NeutroSFE  -  Pharmaceutical SFE / CRM / Distribution Management
-- Physical schema v1.0  (MySQL 8.0+  /  MariaDB 10.4+ compatible)
--
-- Conventions
--   * snake_case, singular FK names  (<table>_id), plural table names
--   * BIGINT UNSIGNED surrogate keys; business codes kept in `code`
--     columns with UNIQUE constraints (matches MREP "ReferenceId",
--     "T.Code", "Dr Code", "Distributor Code" ...)
--   * period_month = INT yyyymm  (e.g. 202610) - used as the partition
--     and aggregation key for every time-series fact table
--   * created_by / updated_by reference users.id but carry no FK on
--     purpose (avoids circular dependencies and keeps bulk loads fast)
--   * High-volume fact tables (sales_*, distributor_stock) are RANGE
--     partitioned by period_month and therefore carry no FKs; integrity
--     is enforced by the import/ETL layer.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- 1. PLATFORM / SYSTEM
-- =====================================================================

CREATE TABLE companies (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(20)  NOT NULL UNIQUE,
  name            VARCHAR(150) NOT NULL,
  logo_path       VARCHAR(255) NULL,
  fiscal_year_start_month TINYINT UNSIGNED NOT NULL DEFAULT 1,
  currency_code   CHAR(3) NOT NULL DEFAULT 'PKR',
  timezone        VARCHAR(40) NOT NULL DEFAULT 'Asia/Karachi',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `group`     VARCHAR(50)  NOT NULL,
  `key`       VARCHAR(100) NOT NULL,
  value       TEXT NULL,
  value_type  ENUM('string','int','decimal','bool','json') NOT NULL DEFAULT 'string',
  description VARCHAR(255) NULL,
  updated_by  BIGINT UNSIGNED NULL,
  updated_at  TIMESTAMP NULL,
  UNIQUE KEY uq_settings (`group`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fiscal calendar (MREP: Common/GetFiscalYearConfig)
CREATE TABLE fiscal_periods (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  period_month  INT UNSIGNED NOT NULL UNIQUE,          -- yyyymm
  fiscal_year   SMALLINT UNSIGNED NOT NULL,
  quarter       TINYINT UNSIGNED NOT NULL,
  month_no      TINYINT UNSIGNED NOT NULL,
  month_label   VARCHAR(20) NOT NULL,
  start_date    DATE NOT NULL,
  end_date      DATE NOT NULL,
  working_days  TINYINT UNSIGNED NULL,
  is_locked     TINYINT(1) NOT NULL DEFAULT 0,          -- sale/expense month closed
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_fp_year (fiscal_year, quarter)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gazetted / public holidays (MREP: Employee/GazettedDays)
CREATE TABLE holidays (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  holiday_date  DATE NOT NULL UNIQUE,
  title         VARCHAR(150) NOT NULL,
  created_by    BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Generic lookups (MREP: Territory/LookupView + Common/GetLookupCategories)
-- Categories seen in reference: PRODUCT FORMULATION, PRODUCT CATEGORY,
-- NOT MET REASON, CHEMIST TYPE, DOCTOR LOCATION TYPE, GENDER,
-- DOCTOR QUALIFICATION, DOCTOR RANK, OBLIGATION TYPE, OBLIGATION BUSINESS
-- OBJECTIVES, PRODUCT PRESCRIPTION OPTION, DESIGNATION TITLES, DOCTOR GRADES,
-- DOCTOR FREQUENCY, EVENT TIME (M/E/F), EXPENSE CATEGORY, PASSWORD POLICY,
-- EMPLOYEE ATTRIBUTES, BUSINESS TYPES, ROI TYPES, CASH TYPES,
-- CHEMIST LOCATION, CHEMIST QUALIFICATION, MEETING TYPE, LEAVE TYPE,
-- PAYMENT MODE, DURATION TYPE, CARE PLAN TYPE, CHANNEL, DOCTOR CATEGORY ...
CREATE TABLE lookup_categories (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code         VARCHAR(60)  NOT NULL UNIQUE,
  name         VARCHAR(120) NOT NULL,
  is_system    TINYINT(1) NOT NULL DEFAULT 0,          -- system categories cannot be deleted
  is_active    TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lookup_values (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id  BIGINT UNSIGNED NOT NULL,
  code         VARCHAR(60)  NULL,
  name         VARCHAR(150) NOT NULL,
  extra        JSON NULL,                               -- e.g. {"start":"09:00","end":"13:00"}
  sort_order   SMALLINT NOT NULL DEFAULT 0,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_at   TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_lookup (category_id, name),
  KEY ix_lookup_code (category_id, code),
  CONSTRAINT fk_lookup_cat FOREIGN KEY (category_id) REFERENCES lookup_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Polymorphic file store (bills, quotation slips, doctor images, presentations ...)
CREATE TABLE attachments (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attachable_type VARCHAR(60) NOT NULL,
  attachable_id  BIGINT UNSIGNED NOT NULL,
  category       VARCHAR(60) NULL,                      -- BILL, QUOTATION, REQUEST_IMAGE, CHEQUE ...
  disk           VARCHAR(30) NOT NULL DEFAULT 'local',
  path           VARCHAR(500) NOT NULL,
  original_name  VARCHAR(255) NULL,
  mime_type      VARCHAR(100) NULL,
  size_bytes     BIGINT UNSIGNED NULL,
  created_by     BIGINT UNSIGNED NULL,
  created_at     TIMESTAMP NULL,
  KEY ix_attach (attachable_type, attachable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bulk import runs (MREP: all Up-Loaders) + row level errors
CREATE TABLE import_batches (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  import_type   VARCHAR(60) NOT NULL,   -- DAILY_SALE, MONTHLY_SALE, SALE_MSM, TARGETS, DOCTORS, DOCTOR_LOCATIONS,
                                        -- TERRITORY_DOCTOR_TAGGING, TERRITORY_CHEMIST_TAGGING, AREA_MAPPING,
                                        -- PRODUCT_MAPPING, DESIGNATION_UPDATE, GIFT_TAGGING, PRODUCT_SAMPLES, STOCK
  period_month  INT UNSIGNED NULL,
  distributor_id BIGINT UNSIGNED NULL,
  file_name     VARCHAR(255) NULL,
  file_path     VARCHAR(500) NULL,
  status        ENUM('queued','validating','processing','completed','completed_with_errors','failed','rolled_back')
                NOT NULL DEFAULT 'queued',
  rows_total    INT UNSIGNED NOT NULL DEFAULT 0,
  rows_ok       INT UNSIGNED NOT NULL DEFAULT 0,
  rows_failed   INT UNSIGNED NOT NULL DEFAULT 0,
  message       TEXT NULL,
  started_at    DATETIME NULL,
  finished_at   DATETIME NULL,
  created_by    BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_ib_type (import_type, period_month),
  KEY ix_ib_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_errors (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  import_batch_id BIGINT UNSIGNED NOT NULL,
  row_no          INT UNSIGNED NULL,
  severity        ENUM('error','warning') NOT NULL DEFAULT 'error',
  issue           VARCHAR(255) NOT NULL,
  detail          TEXT NULL,
  raw_row         JSON NULL,
  KEY ix_ie_batch (import_batch_id),
  CONSTRAINT fk_ie_batch FOREIGN KEY (import_batch_id) REFERENCES import_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Background / scheduled job runs (MREP: Sale Job Sync, Area mapping job, Call log job)
CREATE TABLE job_runs (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_type      VARCHAR(60) NOT NULL,     -- SALE_ALLOCATION, SALES_SUMMARY_REFRESH, CALL_SUMMARY_REFRESH, ROI_REFRESH ...
  period_month  INT UNSIGNED NULL,
  status        ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',
  rows_affected INT UNSIGNED NOT NULL DEFAULT 0,
  message       TEXT NULL,
  started_at    DATETIME NULL,
  finished_at   DATETIME NULL,
  triggered_by  BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NULL,
  KEY ix_jr (job_type, period_month, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 2. GEOGRAPHY  (MREP: Area/RCBMapping  Area > Brick > Town > District > City > Region)
-- =====================================================================

CREATE TABLE provinces (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code  VARCHAR(20)  NOT NULL UNIQUE,
  name  VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE districts (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  province_id  BIGINT UNSIGNED NOT NULL,
  code         VARCHAR(20)  NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  CONSTRAINT fk_district_prov FOREIGN KEY (province_id) REFERENCES provinces(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cities (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  district_id  BIGINT UNSIGNED NOT NULL,
  code         VARCHAR(20)  NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  is_big_city  TINYINT(1) NOT NULL DEFAULT 0,          -- drives "DA Big City" allowance
  latitude     DECIMAL(10,7) NULL,
  longitude    DECIMAL(10,7) NULL,
  CONSTRAINT fk_city_district FOREIGN KEY (district_id) REFERENCES districts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Base towns (employee / doctor / location base town)
CREATE TABLE towns (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  city_id      BIGINT UNSIGNED NOT NULL,
  code         VARCHAR(20)  NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  town_type    ENUM('base','outskirt','outstation') NOT NULL DEFAULT 'base',
  latitude     DECIMAL(10,7) NULL,
  longitude    DECIMAL(10,7) NULL,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_town_city FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Company (IMS) bricks; major brick = parent grouping
CREATE TABLE bricks (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  town_id         BIGINT UNSIGNED NOT NULL,
  major_brick_id  BIGINT UNSIGNED NULL,
  code            VARCHAR(30)  NOT NULL UNIQUE,
  name            VARCHAR(150) NOT NULL,
  category        VARCHAR(40)  NULL,                    -- Brick Category (urban/rural/...)
  is_ims_brick    TINYINT(1) NOT NULL DEFAULT 1,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_brick_town (town_id),
  CONSTRAINT fk_brick_town  FOREIGN KEY (town_id) REFERENCES towns(id),
  CONSTRAINT fk_brick_major FOREIGN KEY (major_brick_id) REFERENCES bricks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Localities / areas inside a brick
CREATE TABLE localities (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brick_id   BIGINT UNSIGNED NOT NULL,
  code       VARCHAR(30)  NOT NULL UNIQUE,
  name       VARCHAR(150) NOT NULL,
  CONSTRAINT fk_locality_brick FOREIGN KEY (brick_id) REFERENCES bricks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Town-to-town distances for mileage claims (MREP: Expense/MileageDatabase)
CREATE TABLE mileage_routes (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_town_id   BIGINT UNSIGNED NOT NULL,
  to_town_id     BIGINT UNSIGNED NOT NULL,
  distance_km    DECIMAL(8,2) NOT NULL,
  effective_from DATE NOT NULL,
  effective_to   DATE NULL,
  is_active      TINYINT(1) NOT NULL DEFAULT 1,
  created_by     BIGINT UNSIGNED NULL,
  created_at     TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_mr (from_town_id, to_town_id, effective_from),
  CONSTRAINT fk_mr_from FOREIGN KEY (from_town_id) REFERENCES towns(id),
  CONSTRAINT fk_mr_to   FOREIGN KEY (to_town_id)   REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 3. ORGANISATION  (Division > Sub-division > Team > Zone > Region > Territory)
-- =====================================================================

CREATE TABLE divisions (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code  VARCHAR(20)  NOT NULL UNIQUE,
  name  VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sub_divisions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  division_id  BIGINT UNSIGNED NOT NULL,
  code         VARCHAR(20)  NOT NULL UNIQUE,
  name         VARCHAR(100) NOT NULL,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_subdiv_div FOREIGN KEY (division_id) REFERENCES divisions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Employee/ManageTeam (ReferenceId, TeamName, ShortName, SubDivision, Division)
CREATE TABLE teams (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sub_division_id  BIGINT UNSIGNED NULL,
  code             VARCHAR(20)  NOT NULL UNIQUE,
  name             VARCHAR(100) NOT NULL,
  short_name       VARCHAR(30)  NULL,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_team_subdiv FOREIGN KEY (sub_division_id) REFERENCES sub_divisions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP designations: CEO, BUH, NSM, SM, DSM, RSM, AHOS, BDM, DM, PM, RDS, MIO, PRODUCT SPECIALIST, TRAINING MANAGER
CREATE TABLE designations (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code             VARCHAR(30)  NOT NULL UNIQUE,
  name             VARCHAR(100) NOT NULL,
  hierarchy_level  TINYINT UNSIGNED NOT NULL,           -- 1 = top (CEO) ... n = field rep
  is_field_force   TINYINT(1) NOT NULL DEFAULT 0,       -- MIO / PA / TM
  is_manager       TINYINT(1) NOT NULL DEFAULT 0,
  can_approve      TINYINT(1) NOT NULL DEFAULT 0,
  is_active        TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Area/ZoneManagement
CREATE TABLE zones (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id   BIGINT UNSIGNED NOT NULL,
  code      VARCHAR(20)  NOT NULL,
  name      VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_zone (team_id, code),
  CONSTRAINT fk_zone_team FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Area/RegionManagement
CREATE TABLE regions (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  zone_id   BIGINT UNSIGNED NOT NULL,
  code      VARCHAR(20)  NOT NULL,
  name      VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_region (zone_id, code),
  CONSTRAINT fk_region_zone FOREIGN KEY (zone_id) REFERENCES zones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Territory/TerritoryManagement + TerritoryHierarchyTree.
-- A territory (T.Code) is a *position* in the hierarchy; employees are assigned to it over time.
CREATE TABLE territories (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30)  NOT NULL UNIQUE,         -- T.Code
  sas_code        VARCHAR(30)  NULL,                    -- SAS / external reference id
  name            VARCHAR(150) NULL,
  team_id         BIGINT UNSIGNED NOT NULL,
  designation_id  BIGINT UNSIGNED NOT NULL,
  region_id       BIGINT UNSIGNED NULL,                 -- RegionTerritories
  parent_id       BIGINT UNSIGNED NULL,
  base_town_id    BIGINT UNSIGNED NULL,
  depth           TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_vacant       TINYINT(1) NOT NULL DEFAULT 1,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_terr_team (team_id, is_active),
  KEY ix_terr_parent (parent_id),
  KEY ix_terr_region (region_id),
  CONSTRAINT fk_terr_team   FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_terr_desig  FOREIGN KEY (designation_id) REFERENCES designations(id),
  CONSTRAINT fk_terr_region FOREIGN KEY (region_id) REFERENCES regions(id),
  CONSTRAINT fk_terr_parent FOREIGN KEY (parent_id) REFERENCES territories(id),
  CONSTRAINT fk_terr_town   FOREIGN KEY (base_town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Closure table: O(1) "all territories under manager X" for data scoping & roll-ups
CREATE TABLE territory_closure (
  ancestor_id    BIGINT UNSIGNED NOT NULL,
  descendant_id  BIGINT UNSIGNED NOT NULL,
  depth          TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id),
  KEY ix_tc_desc (descendant_id, depth),
  CONSTRAINT fk_tc_anc  FOREIGN KEY (ancestor_id)   REFERENCES territories(id) ON DELETE CASCADE,
  CONSTRAINT fk_tc_desc FOREIGN KEY (descendant_id) REFERENCES territories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Field work places used for attendance & expense (MREP: Area/ManageContactPoint, ContactPointList)
CREATE TABLE contact_points (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(30)  NOT NULL UNIQUE,
  name          VARCHAR(150) NOT NULL,
  city_id       BIGINT UNSIGNED NOT NULL,
  town_id       BIGINT UNSIGNED NULL,
  latitude      DECIMAL(10,7) NULL,
  longitude     DECIMAL(10,7) NULL,
  effective_to  DATE NULL,
  deleted_at    TIMESTAMP NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_cp_city FOREIGN KEY (city_id) REFERENCES cities(id),
  CONSTRAINT fk_cp_town FOREIGN KEY (town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE territory_contact_points (
  territory_id      BIGINT UNSIGNED NOT NULL,
  contact_point_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (territory_id, contact_point_id),
  CONSTRAINT fk_tcp_terr FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
  CONSTRAINT fk_tcp_cp   FOREIGN KEY (contact_point_id) REFERENCES contact_points(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 4. EXPENSE GRADES (needed by employees)
-- =====================================================================

-- MREP: Expense/Grade
CREATE TABLE expense_grades (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code       VARCHAR(20)  NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  notes      VARCHAR(255) NULL,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 5. PEOPLE  (MREP: Employee/NewResource, EmployeeListV1/V2, TerritoryEmployeeMapping)
-- =====================================================================

CREATE TABLE employees (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code                  VARCHAR(30)  NOT NULL UNIQUE,   -- Employee Code / ReferenceId
  hr_code               VARCHAR(30)  NULL,
  sap_id                VARCHAR(30)  NULL,
  full_name             VARCHAR(150) NOT NULL,
  gender                ENUM('M','F','O') NULL,
  cnic                  VARCHAR(20)  NULL,
  cnic_remarks          VARCHAR(255) NULL,
  date_of_birth         DATE NULL,
  email                 VARCHAR(150) NULL,
  mobile_personal       VARCHAR(20)  NULL,
  mobile_official       VARCHAR(20)  NULL,
  address               VARCHAR(255) NULL,
  base_town_id          BIGINT UNSIGNED NULL,
  base_team_id          BIGINT UNSIGNED NULL,
  designation_id        BIGINT UNSIGNED NOT NULL,
  designation_title_id  BIGINT UNSIGNED NULL,           -- lookup DESIGNATION TITLES
  expense_grade_id      BIGINT UNSIGNED NULL,
  gross_salary          DECIMAL(12,2) NULL,
  joining_date          DATE NOT NULL,
  effective_date        DATE NULL,
  hr_intimation_date    DATE NULL,
  resigned_date         DATE NULL,
  sim_activation_date   DATE NULL,
  sim_deactivation_date DATE NULL,
  status                ENUM('active','inactive','resigned','terminated','on_leave') NOT NULL DEFAULT 'active',
  profile_image_path    VARCHAR(500) NULL,
  remarks               VARCHAR(500) NULL,
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  KEY ix_emp_status (status),
  KEY ix_emp_desig (designation_id),
  KEY ix_emp_name (full_name),
  CONSTRAINT fk_emp_town  FOREIGN KEY (base_town_id) REFERENCES towns(id),
  CONSTRAINT fk_emp_team  FOREIGN KEY (base_team_id) REFERENCES teams(id),
  CONSTRAINT fk_emp_desig FOREIGN KEY (designation_id) REFERENCES designations(id),
  CONSTRAINT fk_emp_dtitle FOREIGN KEY (designation_title_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_emp_grade FOREIGN KEY (expense_grade_id) REFERENCES expense_grades(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee documents/attributes (MREP: "Employee Attributes" lookup + SaveAttachemts)
CREATE TABLE employee_attributes (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id    BIGINT UNSIGNED NOT NULL,
  attribute_id   BIGINT UNSIGNED NOT NULL,              -- lookup EMPLOYEE ATTRIBUTES
  value          VARCHAR(500) NULL,
  value_date     DATE NULL,
  attachment_id  BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_ea_emp (employee_id),
  CONSTRAINT fk_ea_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_ea_attr FOREIGN KEY (attribute_id) REFERENCES lookup_values(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee <-> Territory over time (Assigned/Unassigned, Last Employee, vacancy history)
CREATE TABLE territory_assignments (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  territory_id  BIGINT UNSIGNED NOT NULL,
  employee_id   BIGINT UNSIGNED NOT NULL,
  from_date     DATE NOT NULL,
  to_date       DATE NULL,
  is_primary    TINYINT(1) NOT NULL DEFAULT 1,
  reason        VARCHAR(100) NULL,                      -- JOINING, TRANSFER, PROMOTION, RESIGNATION ...
  created_by    BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NULL,
  KEY ix_ta_terr (territory_id, from_date, to_date),
  KEY ix_ta_emp (employee_id, from_date, to_date),
  CONSTRAINT fk_ta_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_ta_emp  FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mobile devices (IMEI binding, app version, "Clear Device ID")
CREATE TABLE employee_devices (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id   BIGINT UNSIGNED NOT NULL,
  device_id     VARCHAR(100) NOT NULL,
  device_model  VARCHAR(100) NULL,
  os_version    VARCHAR(50)  NULL,
  app_version   VARCHAR(30)  NULL,
  push_token    VARCHAR(255) NULL,
  registered_at DATETIME NOT NULL,
  last_seen_at  DATETIME NULL,
  cleared_at    DATETIME NULL,
  cleared_by    BIGINT UNSIGNED NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_ed_emp (employee_id, is_active),
  KEY ix_ed_dev (device_id),
  CONSTRAINT fk_ed_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 6. SECURITY: users, roles, menu permissions  (MREP: Employee/ManageMenu, Settings/FavouriteMenu)
-- =====================================================================

CREATE TABLE users (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id          BIGINT UNSIGNED NULL UNIQUE,
  username             VARCHAR(60)  NOT NULL UNIQUE,
  name                 VARCHAR(150) NOT NULL,
  email                VARCHAR(150) NULL UNIQUE,
  password             VARCHAR(255) NOT NULL,
  mfa_enabled          TINYINT(1) NOT NULL DEFAULT 0,
  mfa_secret           TEXT NULL,                       -- encrypted at rest (app key)
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  password_changed_at  DATETIME NULL,
  last_login_at        DATETIME NULL,
  last_login_ip        VARCHAR(45) NULL,
  failed_logins        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until         DATETIME NULL,
  data_scope           ENUM('own','hierarchy','team','all') NOT NULL DEFAULT 'hierarchy',
  is_active            TINYINT(1) NOT NULL DEFAULT 1,
  remember_token       VARCHAR(100) NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_user_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extra teams a user may see (head-office users spanning several teams)
CREATE TABLE user_teams (
  user_id  BIGINT UNSIGNED NOT NULL,
  team_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, team_id),
  CONSTRAINT fk_ut_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ut_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(50)  NOT NULL UNIQUE,             -- SUPER_ADMIN, HO_ADMIN, SALES_ADMIN, HR, FINANCE, NSM, RSM, MIO ...
  name        VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_system   TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
  user_id  BIGINT UNSIGNED NOT NULL,
  role_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Navigation tree = permission tree (one row per module / page)
CREATE TABLE menus (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id   BIGINT UNSIGNED NULL,
  code        VARCHAR(100) NOT NULL UNIQUE,             -- e.g. reports.sales.brick-wise
  title       VARCHAR(150) NOT NULL,
  route       VARCHAR(200) NULL,
  icon        VARCHAR(50)  NULL,
  description VARCHAR(500) NULL,                        -- "Report Objective" popup
  sort_order  SMALLINT NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_menu_parent FOREIGN KEY (parent_id) REFERENCES menus(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_menu_permissions (
  role_id        BIGINT UNSIGNED NOT NULL,
  menu_id        BIGINT UNSIGNED NOT NULL,
  can_view       TINYINT(1) NOT NULL DEFAULT 1,
  can_add        TINYINT(1) NOT NULL DEFAULT 0,
  can_update     TINYINT(1) NOT NULL DEFAULT 0,
  can_delete     TINYINT(1) NOT NULL DEFAULT 0,
  can_download   TINYINT(1) NOT NULL DEFAULT 0,
  can_approve    TINYINT(1) NOT NULL DEFAULT 0,
  show_location  TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (role_id, menu_id),
  CONSTRAINT fk_rmp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rmp_menu FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-user override (MREP "Module Management" works per employee)
CREATE TABLE user_menu_permissions (
  user_id        BIGINT UNSIGNED NOT NULL,
  menu_id        BIGINT UNSIGNED NOT NULL,
  can_view       TINYINT(1) NULL,
  can_add        TINYINT(1) NULL,
  can_update     TINYINT(1) NULL,
  can_delete     TINYINT(1) NULL,
  can_download   TINYINT(1) NULL,
  can_approve    TINYINT(1) NULL,
  show_location  TINYINT(1) NULL,
  PRIMARY KEY (user_id, menu_id),
  CONSTRAINT fk_ump_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ump_menu FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_favourite_menus (
  user_id    BIGINT UNSIGNED NOT NULL,
  menu_id    BIGINT UNSIGNED NOT NULL,
  sort_order SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, menu_id),
  CONSTRAINT fk_ufm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ufm_menu FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saved grid layouts / report views (improvement over MREP: user-defined report variants)
CREATE TABLE saved_report_views (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  report_key  VARCHAR(100) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  state       JSON NOT NULL,                            -- filters, columns, grouping, pivot, sort
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  is_shared   TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_srv (user_id, report_key),
  CONSTRAINT fk_srv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Browsing / API history (MREP: Employee/UserHistory)
CREATE TABLE user_activity_logs (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT UNSIGNED NULL,
  menu_code     VARCHAR(100) NULL,
  action        VARCHAR(100) NULL,
  http_method   VARCHAR(10)  NULL,
  url           VARCHAR(500) NULL,
  params        JSON NULL,
  response_code SMALLINT NULL,
  source        ENUM('web','mobile','api') NOT NULL DEFAULT 'web',
  ip_address    VARCHAR(45)  NULL,
  user_agent    VARCHAR(255) NULL,
  device_id     VARCHAR(100) NULL,
  created_at    DATETIME NOT NULL,
  KEY ix_ual_user (user_id, created_at),
  KEY ix_ual_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data change audit trail (who changed what, old/new values)
CREATE TABLE audit_logs (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        BIGINT UNSIGNED NULL,
  auditable_type VARCHAR(60) NOT NULL,
  auditable_id   BIGINT UNSIGNED NOT NULL,
  event          ENUM('created','updated','deleted','restored','approved','rejected') NOT NULL,
  old_values     JSON NULL,
  new_values     JSON NULL,
  ip_address     VARCHAR(45) NULL,
  created_at     DATETIME NOT NULL,
  KEY ix_audit_entity (auditable_type, auditable_id),
  KEY ix_audit_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Push / in-app notifications (MREP: Reports/Notifications)
CREATE TABLE notifications (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_user_id   BIGINT UNSIGNED NULL,
  title            VARCHAR(200) NOT NULL,
  body             TEXT NOT NULL,
  category         VARCHAR(50) NULL,                    -- ANNOUNCEMENT, APPROVAL, ALERT, LOCATION ...
  link_route       VARCHAR(200) NULL,
  image_path       VARCHAR(500) NULL,
  created_at       DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_recipients (
  notification_id  BIGINT UNSIGNED NOT NULL,
  employee_id      BIGINT UNSIGNED NOT NULL,
  status           ENUM('sent','delivered','read','failed') NOT NULL DEFAULT 'sent',
  status_at        DATETIME NULL,
  notes            VARCHAR(255) NULL,
  PRIMARY KEY (notification_id, employee_id),
  KEY ix_nr_emp (employee_id, status),
  CONSTRAINT fk_nr_notif FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  CONSTRAINT fk_nr_emp   FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 7. GENERIC MULTI-LEVEL APPROVAL ENGINE
--    Used by: doctor/location requests, work plans, expenses, obligations,
--    event-log permissions, leave.  MREP shows up to 4 manager levels + admin.
-- =====================================================================

CREATE TABLE approval_workflows (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(60) NOT NULL UNIQUE,            -- EXPENSE, OBLIGATION, DOCTOR_REQUEST, WORK_PLAN ...
  name          VARCHAR(100) NOT NULL,
  team_id       BIGINT UNSIGNED NULL,                   -- NULL = all teams
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_aw_team FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE approval_workflow_levels (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workflow_id      BIGINT UNSIGNED NOT NULL,
  level_no         TINYINT UNSIGNED NOT NULL,
  approver_type    ENUM('line_manager','designation','role','specific_user') NOT NULL,
  designation_id   BIGINT UNSIGNED NULL,
  role_id          BIGINT UNSIGNED NULL,
  user_id          BIGINT UNSIGNED NULL,
  min_amount       DECIMAL(14,2) NULL,                  -- level applies only above this value
  can_edit_amount  TINYINT(1) NOT NULL DEFAULT 0,
  sla_hours        SMALLINT UNSIGNED NULL,
  UNIQUE KEY uq_awl (workflow_id, level_no),
  CONSTRAINT fk_awl_wf FOREIGN KEY (workflow_id) REFERENCES approval_workflows(id) ON DELETE CASCADE,
  CONSTRAINT fk_awl_desig FOREIGN KEY (designation_id) REFERENCES designations(id),
  CONSTRAINT fk_awl_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE approval_actions (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  approvable_type     VARCHAR(60) NOT NULL,
  approvable_id       BIGINT UNSIGNED NOT NULL,
  level_no            TINYINT UNSIGNED NOT NULL,
  approver_employee_id BIGINT UNSIGNED NULL,
  approver_user_id    BIGINT UNSIGNED NULL,
  status              ENUM('pending','approved','rejected','returned','skipped') NOT NULL DEFAULT 'pending',
  notes               VARCHAR(1000) NULL,
  amount_before       DECIMAL(14,2) NULL,
  amount_after        DECIMAL(14,2) NULL,
  latitude            DECIMAL(10,7) NULL,               -- manager approves location on-site
  longitude           DECIMAL(10,7) NULL,
  address             VARCHAR(255) NULL,
  acted_at            DATETIME NULL,
  created_at          TIMESTAMP NULL,
  KEY ix_aa_entity (approvable_type, approvable_id, level_no),
  KEY ix_aa_pending (approver_employee_id, status),
  CONSTRAINT fk_aa_emp FOREIGN KEY (approver_employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 8. PRODUCTS  (MREP: Product/Product, TagProduct, PTM, ProductSample/Management)
-- =====================================================================

CREATE TABLE brands (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code       VARCHAR(30)  NOT NULL UNIQUE,
  name       VARCHAR(150) NOT NULL,
  is_active  TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- National sale groups (MREP: Reports/NationalGroupWise)
CREATE TABLE product_groups (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code  VARCHAR(30)  NOT NULL UNIQUE,
  name  VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code                VARCHAR(30)  NOT NULL UNIQUE,     -- SKU ID / Product Reference Id
  erp_code            VARCHAR(30)  NULL,                -- AX / ERP code
  name                VARCHAR(200) NOT NULL,
  pack_size           VARCHAR(60)  NULL,
  strength            VARCHAR(60)  NULL,
  brand_id            BIGINT UNSIGNED NULL,
  product_group_id    BIGINT UNSIGNED NULL,
  category_id         BIGINT UNSIGNED NULL,             -- lookup PRODUCT CATEGORY
  formulation_id      BIGINT UNSIGNED NULL,             -- lookup PRODUCT FORMULATION
  product_manager_id  BIGINT UNSIGNED NULL,             -- employees.id
  unit_price          DECIMAL(12,4) NULL,               -- current (denormalised from product_prices)
  trade_price         DECIMAL(12,4) NULL,
  launch_date         DATE NULL,
  is_focus            TINYINT(1) NOT NULL DEFAULT 0,
  status              ENUM('active','inactive','discontinued') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  KEY ix_prod_brand (brand_id),
  KEY ix_prod_erp (erp_code),
  CONSTRAINT fk_prod_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
  CONSTRAINT fk_prod_group FOREIGN KEY (product_group_id) REFERENCES product_groups(id),
  CONSTRAINT fk_prod_cat   FOREIGN KEY (category_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_prod_form  FOREIGN KEY (formulation_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_prod_pm    FOREIGN KEY (product_manager_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shared products: one SKU promoted by several teams
CREATE TABLE product_teams (
  product_id  BIGINT UNSIGNED NOT NULL,
  team_id     BIGINT UNSIGNED NOT NULL,
  from_date   DATE NOT NULL,
  to_date     DATE NULL,
  PRIMARY KEY (product_id, team_id, from_date),
  KEY ix_pt_team (team_id),
  CONSTRAINT fk_pt_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_pt_team FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Price history ("Updated Trade Price", "Effected Date")
CREATE TABLE product_prices (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id      BIGINT UNSIGNED NOT NULL,
  trade_price     DECIMAL(12,4) NOT NULL,
  unit_price      DECIMAL(12,4) NULL,
  retail_price    DECIMAL(12,4) NULL,
  effective_from  DATE NOT NULL,
  effective_to    DATE NULL,
  created_by      BIGINT UNSIGNED NULL,
  created_at      TIMESTAMP NULL,
  UNIQUE KEY uq_pp (product_id, effective_from),
  CONSTRAINT fk_pp_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product <-> responsible territory (MREP: Product/PTM "Product Target Management")
CREATE TABLE product_territory_managers (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT UNSIGNED NOT NULL,
  territory_id  BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_ptm (product_id, territory_id),
  CONSTRAINT fk_ptm_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_ptm_terr FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Callevent/ProductCompetitors
CREATE TABLE competitor_products (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT UNSIGNED NOT NULL,              -- our product it competes with
  name          VARCHAR(200) NOT NULL,
  company_name  VARCHAR(150) NOT NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_cp_prod (product_id),
  CONSTRAINT fk_comp_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 9. PROMOTIONAL INVENTORY: samples & gifts / giveaways
--    (MREP: ProductSample/Management, Callevent/ManageGiftAndGiveaways,
--     Uploader/ProductSampleUploader, GiftAndGiveawaysTerritoryTaggingBulk,
--     Reports/ProductPosition, GiftAndGiveawaysConsumptionReprot)
-- =====================================================================

CREATE TABLE promo_items (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_type          ENUM('sample','gift') NOT NULL,
  code               VARCHAR(30)  NOT NULL UNIQUE,
  name               VARCHAR(200) NOT NULL,
  description        VARCHAR(500) NULL,
  product_id         BIGINT UNSIGNED NULL,              -- sample of which commercial SKU
  brand_id           BIGINT UNSIGNED NULL,
  team_id            BIGINT UNSIGNED NULL,
  category_id        BIGINT UNSIGNED NULL,
  formulation_id     BIGINT UNSIGNED NULL,
  unit_value         DECIMAL(12,4) NULL,                -- estimated value per unit
  trade_price        DECIMAL(12,4) NULL,
  valid_from         DATE NULL,
  valid_to           DATE NULL,
  launch_date        DATE NULL,
  status             ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  KEY ix_pi_type (item_type, status),
  CONSTRAINT fk_pi_prod  FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_pi_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
  CONSTRAINT fk_pi_team  FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Allocation / dispatch to a territory (header of an inbound movement)
CREATE TABLE promo_allocations (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  promo_item_id        BIGINT UNSIGNED NOT NULL,
  territory_id         BIGINT UNSIGNED NOT NULL,
  employee_id          BIGINT UNSIGNED NULL,
  quantity             INT NOT NULL,
  batch_no             VARCHAR(50) NULL,
  expiry_date          DATE NULL,
  estimated_delivery   DATE NULL,
  shipment_address     VARCHAR(255) NULL,
  consignment_no       VARCHAR(60) NULL,
  status               ENUM('dispatched','received','returned','cancelled') NOT NULL DEFAULT 'dispatched',
  received_at          DATETIME NULL,
  import_batch_id      BIGINT UNSIGNED NULL,
  created_by           BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_pa_terr (territory_id, promo_item_id),
  CONSTRAINT fk_pa_item FOREIGN KEY (promo_item_id) REFERENCES promo_items(id),
  CONSTRAINT fk_pa_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_pa_emp  FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stock ledger per territory (opening / received / issued / closing is derived)
CREATE TABLE promo_stock_movements (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  promo_item_id  BIGINT UNSIGNED NOT NULL,
  territory_id   BIGINT UNSIGNED NOT NULL,
  employee_id    BIGINT UNSIGNED NULL,
  movement_date  DATE NOT NULL,
  movement_type  ENUM('allocation','issue','return','adjustment','transfer_in','transfer_out','expiry') NOT NULL,
  quantity       INT NOT NULL,                          -- +in / -out
  batch_no       VARCHAR(50) NULL,
  ref_type       VARCHAR(40) NULL,                      -- promo_allocation / call_event / manual
  ref_id         BIGINT UNSIGNED NULL,
  doctor_id      BIGINT UNSIGNED NULL,
  notes          VARCHAR(255) NULL,
  created_at     TIMESTAMP NULL,
  KEY ix_psm_terr (territory_id, promo_item_id, movement_date),
  KEY ix_psm_ref (ref_type, ref_id),
  CONSTRAINT fk_psm_item FOREIGN KEY (promo_item_id) REFERENCES promo_items(id),
  CONSTRAINT fk_psm_terr FOREIGN KEY (territory_id) REFERENCES territories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 10. CUSTOMERS - DOCTORS & PRACTICE PLACES  (MREP: Doctor/* )
-- =====================================================================

CREATE TABLE specialties (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(30)  NOT NULL UNIQUE,
  name        VARCHAR(120) NOT NULL,
  group_name  VARCHAR(120) NULL,                        -- "Speciality Group"
  is_active   TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Doctor/CustomerGrade (TeamName, GradeName, Frequency, Type, Status)
CREATE TABLE customer_grades (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id        BIGINT UNSIGNED NOT NULL,
  customer_type  ENUM('doctor','chemist') NOT NULL DEFAULT 'doctor',
  name           VARCHAR(20) NOT NULL,                  -- A / B / C / KOL ...
  visit_frequency TINYINT UNSIGNED NOT NULL,            -- required visits per month
  is_active      TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_cg (team_id, customer_type, name),
  CONSTRAINT fk_cg_team FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE doctors (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code               VARCHAR(30)  NOT NULL UNIQUE,      -- Dr Code
  title              VARCHAR(20)  NULL,
  full_name          VARCHAR(150) NOT NULL,
  gender             ENUM('M','F','O') NULL,
  date_of_birth      DATE NULL,
  pmdc_number        VARCHAR(30)  NULL,
  cnic               VARCHAR(20)  NULL,
  cnic_remarks       VARCHAR(255) NULL,
  specialty_id       BIGINT UNSIGNED NULL,
  qualification_id   BIGINT UNSIGNED NULL,              -- lookup DOCTOR QUALIFICATION
  qualification_text VARCHAR(150) NULL,
  doctor_class_id    BIGINT UNSIGNED NULL,              -- lookup DOCTOR RANK / class
  category_id        BIGINT UNSIGNED NULL,              -- lookup DOCTOR CATEGORY
  persona            VARCHAR(60)  NULL,
  email              VARCHAR(150) NULL,
  mobile_1           VARCHAR(20)  NULL,
  mobile_2           VARCHAR(20)  NULL,
  city_id            BIGINT UNSIGNED NULL,
  consultation_fee   DECIMAL(10,2) NULL,
  patients_per_day   SMALLINT UNSIGNED NULL,
  email_consent      TINYINT(1) NULL,
  phone_consent      TINYINT(1) NULL,
  consent_updated_at DATETIME NULL,
  image_path         VARCHAR(500) NULL,
  status             ENUM('pending','active','inactive','rejected') NOT NULL DEFAULT 'pending',
  approved_by        BIGINT UNSIGNED NULL,
  approved_at        DATETIME NULL,
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  KEY ix_doc_name (full_name),
  KEY ix_doc_pmdc (pmdc_number),
  KEY ix_doc_status (status),
  CONSTRAINT fk_doc_spec  FOREIGN KEY (specialty_id) REFERENCES specialties(id),
  CONSTRAINT fk_doc_qual  FOREIGN KEY (qualification_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_doc_class FOREIGN KEY (doctor_class_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_doc_cat   FOREIGN KEY (category_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_doc_city  FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hospitals / clinics / any call location (MREP: Doctor/AllPracticePlaceBaseTown)
CREATE TABLE practice_places (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code             VARCHAR(30)  NOT NULL UNIQUE,        -- Location ID
  name             VARCHAR(200) NOT NULL,
  location_type_id BIGINT UNSIGNED NULL,                -- lookup DOCTOR LOCATION TYPE (Hospital=1, Clinic=2 ...)
  address          VARCHAR(300) NULL,
  phone            VARCHAR(30)  NULL,
  brick_id         BIGINT UNSIGNED NULL,
  locality_id      BIGINT UNSIGNED NULL,
  town_id          BIGINT UNSIGNED NULL,                -- base town
  city_id          BIGINT UNSIGNED NULL,
  latitude         DECIMAL(10,7) NULL,
  longitude        DECIMAL(10,7) NULL,
  on_radius_m      SMALLINT UNSIGNED NOT NULL DEFAULT 100,   -- "ON location" threshold
  near_radius_m    SMALLINT UNSIGNED NOT NULL DEFAULT 300,   -- "NEAR location" threshold
  is_geo_tagged    TINYINT(1) GENERATED ALWAYS AS (latitude IS NOT NULL AND longitude IS NOT NULL) STORED,
  image_path       VARCHAR(500) NULL,
  status           ENUM('pending','active','inactive','rejected') NOT NULL DEFAULT 'pending',
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  KEY ix_pp_brick (brick_id),
  KEY ix_pp_town (town_id),
  KEY ix_pp_geo (latitude, longitude),
  CONSTRAINT fk_ppl_type  FOREIGN KEY (location_type_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_ppl_brick FOREIGN KEY (brick_id) REFERENCES bricks(id),
  CONSTRAINT fk_ppl_loc   FOREIGN KEY (locality_id) REFERENCES localities(id),
  CONSTRAINT fk_ppl_town  FOREIGN KEY (town_id) REFERENCES towns(id),
  CONSTRAINT fk_ppl_city  FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Doctor practises at location (one doctor -> many locations)
CREATE TABLE doctor_locations (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doctor_id          BIGINT UNSIGNED NOT NULL,
  practice_place_id  BIGINT UNSIGNED NOT NULL,
  opd_number         VARCHAR(30) NULL,
  opd_name           VARCHAR(100) NULL,
  timing             VARCHAR(100) NULL,
  is_primary         TINYINT(1) NOT NULL DEFAULT 0,
  status             ENUM('pending','active','inactive','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_dl (doctor_id, practice_place_id),
  KEY ix_dl_pp (practice_place_id),
  CONSTRAINT fk_dl_doc FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_dl_pp  FOREIGN KEY (practice_place_id) REFERENCES practice_places(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Target doctor list per territory (MREP: Territory/AddEmployeeDoctorMapping, Uploader/TerritoryDoctorTagging)
CREATE TABLE territory_doctors (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  territory_id        BIGINT UNSIGNED NOT NULL,
  doctor_location_id  BIGINT UNSIGNED NOT NULL,
  doctor_id           BIGINT UNSIGNED NOT NULL,          -- denormalised for fast filtering
  grade_id            BIGINT UNSIGNED NULL,
  visit_frequency     TINYINT UNSIGNED NOT NULL DEFAULT 2,
  status              ENUM('pending','active','untagged','rejected') NOT NULL DEFAULT 'active',
  tagged_at           DATETIME NULL,
  untagged_at         DATETIME NULL,
  import_batch_id     BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_td (territory_id, doctor_location_id),
  KEY ix_td_doc (doctor_id),
  KEY ix_td_status (territory_id, status),
  CONSTRAINT fk_td_terr  FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_td_dl    FOREIGN KEY (doctor_location_id) REFERENCES doctor_locations(id),
  CONSTRAINT fk_td_doc   FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_td_grade FOREIGN KEY (grade_id) REFERENCES customer_grades(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Field-originated change requests (new doctor, edit doctor, new/changed location, geo-tag, tag/untag)
-- MREP: DoctorApproval, LocationApproval, DoctorAndLocationApproval, TrackLocation, EditSuggestedDoctorsReport
CREATE TABLE customer_change_requests (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_type        ENUM('new_doctor','edit_doctor','new_location','change_location','geo_tag',
                           'tag_doctor','untag_doctor','new_chemist','edit_chemist') NOT NULL,
  doctor_id           BIGINT UNSIGNED NULL,
  practice_place_id   BIGINT UNSIGNED NULL,
  chemist_id          BIGINT UNSIGNED NULL,
  territory_id        BIGINT UNSIGNED NULL,
  requested_by        BIGINT UNSIGNED NOT NULL,          -- employees.id
  requested_at        DATETIME NOT NULL,
  request_latitude    DECIMAL(10,7) NULL,
  request_longitude   DECIMAL(10,7) NULL,
  request_address     VARCHAR(300) NULL,
  payload             JSON NOT NULL,                     -- proposed values
  current_level       TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status              ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  final_decision_at   DATETIME NULL,
  admin_notes         VARCHAR(1000) NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_ccr_status (status, request_type),
  KEY ix_ccr_req (requested_by, requested_at),
  CONSTRAINT fk_ccr_doc  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_ccr_pp   FOREIGN KEY (practice_place_id) REFERENCES practice_places(id),
  CONSTRAINT fk_ccr_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_ccr_emp  FOREIGN KEY (requested_by) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 11. DISTRIBUTION  (MREP: Distribution/*, Area/TerritoryAreaDistribution)
-- =====================================================================

CREATE TABLE distributors (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(30)  NOT NULL UNIQUE,           -- Distributor Code
  erp_code      VARCHAR(30)  NULL,
  sap_id        VARCHAR(30)  NULL,
  name          VARCHAR(200) NOT NULL,
  address       VARCHAR(300) NULL,
  city_id       BIGINT UNSIGNED NULL,
  district_id   BIGINT UNSIGNED NULL,
  town_id       BIGINT UNSIGNED NULL,                   -- base area
  ntn           VARCHAR(30)  NULL,
  phone         VARCHAR(30)  NULL,
  email         VARCHAR(150) NULL,
  weekly_off_day TINYINT UNSIGNED NULL,                 -- 0=Sun..6=Sat
  data_feed_type ENUM('daily','monthly','msm','api','manual') NOT NULL DEFAULT 'daily',
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at TIMESTAMP NULL,
  CONSTRAINT fk_dist_city FOREIGN KEY (city_id) REFERENCES cities(id),
  CONSTRAINT fk_dist_district FOREIGN KEY (district_id) REFERENCES districts(id),
  CONSTRAINT fk_dist_town FOREIGN KEY (town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which teams a distributor serves (MREP: Product/TagProduct, shared distributor reports)
CREATE TABLE distributor_teams (
  distributor_id BIGINT UNSIGNED NOT NULL,
  team_id        BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (distributor_id, team_id),
  CONSTRAINT fk_dt_dist FOREIGN KEY (distributor_id) REFERENCES distributors(id) ON DELETE CASCADE,
  CONSTRAINT fk_dt_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Distributor's own SKU codes -> company product (MREP: DistributorProductMapping, ProductMappingBulk, UnmappedProducts)
CREATE TABLE distributor_products (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  distributor_id       BIGINT UNSIGNED NOT NULL,
  dist_product_code    VARCHAR(50)  NOT NULL,
  dist_product_name    VARCHAR(200) NULL,
  product_id           BIGINT UNSIGNED NULL,            -- NULL = unmapped
  conversion_factor    DECIMAL(10,4) NOT NULL DEFAULT 1, -- distributor pack -> company unit
  mapped_by            BIGINT UNSIGNED NULL,
  mapped_at            DATETIME NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_dp (distributor_id, dist_product_code),
  KEY ix_dp_prod (product_id),
  CONSTRAINT fk_dp_dist FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_dp_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Distributor's own brick codes -> company brick (MREP: UnmappedBricksReport, UnmappedBricks)
CREATE TABLE distributor_bricks (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  distributor_id    BIGINT UNSIGNED NOT NULL,
  dist_brick_code   VARCHAR(50)  NOT NULL,
  dist_brick_name   VARCHAR(200) NULL,
  brick_id          BIGINT UNSIGNED NULL,               -- NULL = unmapped
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_db (distributor_id, dist_brick_code),
  KEY ix_db_brick (brick_id),
  CONSTRAINT fk_db_dist  FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_db_brick FOREIGN KEY (brick_id) REFERENCES bricks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sale split rules: distributor-brick x team -> territory share %
-- (MREP: TerritoryAreaDistribution, BrickRepWiseAreaPercentage, BrickTCodeWiseAreaPercentage,
--  InvalidBrickShare, Uploader/TerritoryAreaMappingBulk)
-- Rule: SUM(share_pct) per (distributor_brick_id, team_id, product_id, period) must equal 100.
CREATE TABLE brick_territory_shares (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  distributor_brick_id BIGINT UNSIGNED NOT NULL,
  team_id              BIGINT UNSIGNED NOT NULL,
  territory_id         BIGINT UNSIGNED NOT NULL,
  product_id           BIGINT UNSIGNED NULL,            -- NULL = all products of the team
  share_pct            DECIMAL(6,3) NOT NULL,
  effective_from       DATE NOT NULL,
  effective_to         DATE NULL,
  import_batch_id      BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_bts_lookup (distributor_brick_id, team_id, effective_from),
  KEY ix_bts_terr (territory_id),
  CONSTRAINT ck_bts_pct CHECK (share_pct >= 0 AND share_pct <= 100),
  CONSTRAINT fk_bts_db   FOREIGN KEY (distributor_brick_id) REFERENCES distributor_bricks(id),
  CONSTRAINT fk_bts_team FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_bts_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_bts_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Retail / institutional customers of distributors (MREP: Distribution/Chemists, InvalidChemist)
CREATE TABLE chemists (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  distributor_id        BIGINT UNSIGNED NOT NULL,
  dist_customer_code    VARCHAR(50)  NOT NULL,          -- Distributor Chemist Code
  name                  VARCHAR(200) NOT NULL,
  chemist_type_id       BIGINT UNSIGNED NULL,           -- lookup CHEMIST TYPE (Retail, Wholesale, Institution, Hospital ...)
  channel               VARCHAR(40)  NULL,
  category              VARCHAR(40)  NULL,
  address               VARCHAR(300) NULL,
  phone                 VARCHAR(30)  NULL,
  license_no            VARCHAR(60)  NULL,
  license_expiry        DATE NULL,
  distributor_brick_id  BIGINT UNSIGNED NULL,
  brick_id              BIGINT UNSIGNED NULL,
  locality_id           BIGINT UNSIGNED NULL,
  town_id               BIGINT UNSIGNED NULL,
  latitude              DECIMAL(10,7) NULL,
  longitude             DECIMAL(10,7) NULL,
  is_valid              TINYINT(1) NOT NULL DEFAULT 1,
  status                ENUM('active','inactive') NOT NULL DEFAULT 'active',
  first_seen_month      INT UNSIGNED NULL,              -- "CreatedMonth" (new/existing/lost analysis)
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_chem (distributor_id, dist_customer_code),
  KEY ix_chem_brick (brick_id),
  KEY ix_chem_name (name),
  CONSTRAINT fk_chem_dist  FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_chem_type  FOREIGN KEY (chemist_type_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_chem_db    FOREIGN KEY (distributor_brick_id) REFERENCES distributor_bricks(id),
  CONSTRAINT fk_chem_brick FOREIGN KEY (brick_id) REFERENCES bricks(id),
  CONSTRAINT fk_chem_loc   FOREIGN KEY (locality_id) REFERENCES localities(id),
  CONSTRAINT fk_chem_town  FOREIGN KEY (town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Distribution/TerritoryChemistList, Uploader/TerritoryChemistTagging
CREATE TABLE territory_chemists (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  territory_id     BIGINT UNSIGNED NOT NULL,
  chemist_id       BIGINT UNSIGNED NOT NULL,
  visit_frequency  TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status           ENUM('active','untagged') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_tch (territory_id, chemist_id),
  KEY ix_tch_chem (chemist_id),
  CONSTRAINT fk_tch_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_tch_chem FOREIGN KEY (chemist_id) REFERENCES chemists(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feed completeness per distributor per day (MREP: DistributorsTextDataStatus)
CREATE TABLE distributor_feed_status (
  distributor_id   BIGINT UNSIGNED NOT NULL,
  data_date        DATE NOT NULL,
  status           ENUM('received','missing','holiday','error') NOT NULL,
  rows_received    INT UNSIGNED NOT NULL DEFAULT 0,
  net_amount       DECIMAL(16,2) NULL,
  import_batch_id  BIGINT UNSIGNED NULL,
  updated_at       TIMESTAMP NULL,
  PRIMARY KEY (distributor_id, data_date),
  CONSTRAINT fk_dfs_dist FOREIGN KEY (distributor_id) REFERENCES distributors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 12. SALES FACTS  (partitioned, FK-less by design)
-- =====================================================================

-- 12.1 Raw secondary sales lines as received from distributors (invoice level)
CREATE TABLE sales_transactions (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_month          INT UNSIGNED NOT NULL,
  transaction_date      DATE NOT NULL,
  distributor_id        BIGINT UNSIGNED NOT NULL,
  invoice_no            VARCHAR(50)  NULL,
  transaction_type      ENUM('sale','return','institution_sale','institution_return','bonus','adjustment') NOT NULL DEFAULT 'sale',
  dist_customer_code    VARCHAR(50)  NULL,
  chemist_id            BIGINT UNSIGNED NULL,
  dist_brick_code       VARCHAR(50)  NULL,
  distributor_brick_id  BIGINT UNSIGNED NULL,
  dist_product_code     VARCHAR(50)  NOT NULL,
  product_id            BIGINT UNSIGNED NULL,           -- NULL until mapped
  batch_no              VARCHAR(50)  NULL,
  trade_price           DECIMAL(12,4) NULL,             -- company TP valid on transaction_date
  dist_price            DECIMAL(12,4) NULL,             -- "Text Data Price"
  quantity              INT NOT NULL DEFAULT 0,         -- signed: returns negative
  bonus_quantity        INT NOT NULL DEFAULT 0,
  gross_amount          DECIMAL(16,2) NOT NULL DEFAULT 0,
  discount_amount       DECIMAL(16,2) NOT NULL DEFAULT 0,
  net_amount            DECIMAL(16,2) NOT NULL DEFAULT 0,   -- "Text Data Net Amount"
  tp_value              DECIMAL(16,2) NOT NULL DEFAULT 0,   -- quantity * trade_price (company valuation)
  is_mapped             TINYINT(1) NOT NULL DEFAULT 0,
  import_batch_id       BIGINT UNSIGNED NOT NULL,
  created_at            TIMESTAMP NULL,
  PRIMARY KEY (id, period_month),
  KEY ix_st_dist_date (distributor_id, transaction_date),
  KEY ix_st_prod (product_id, period_month),
  KEY ix_st_brick (distributor_brick_id, period_month),
  KEY ix_st_chem (chemist_id),
  KEY ix_st_batch (import_batch_id),
  KEY ix_st_unmapped (is_mapped, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (period_month) (
  PARTITION p2024 VALUES LESS THAN (202501),
  PARTITION p2025 VALUES LESS THAN (202601),
  PARTITION p2026 VALUES LESS THAN (202701),
  PARTITION p2027 VALUES LESS THAN (202801),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 12.2 Territory-credited sales (output of the allocation job: transaction x share rule)
CREATE TABLE sales_allocations (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_month          INT UNSIGNED NOT NULL,
  transaction_date      DATE NOT NULL,
  sales_transaction_id  BIGINT UNSIGNED NOT NULL,
  share_rule_id         BIGINT UNSIGNED NULL,
  territory_id          BIGINT UNSIGNED NOT NULL,
  employee_id           BIGINT UNSIGNED NULL,           -- incumbent on transaction_date (NULL = vacant)
  team_id               BIGINT UNSIGNED NOT NULL,
  zone_id               BIGINT UNSIGNED NULL,
  region_id             BIGINT UNSIGNED NULL,
  distributor_id        BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED NOT NULL,
  brand_id              BIGINT UNSIGNED NULL,
  chemist_id            BIGINT UNSIGNED NULL,
  brick_id              BIGINT UNSIGNED NULL,
  transaction_type      ENUM('sale','return','institution_sale','institution_return','bonus','adjustment') NOT NULL,
  share_pct             DECIMAL(6,3) NOT NULL,
  units                 DECIMAL(14,3) NOT NULL DEFAULT 0,
  bonus_units           DECIMAL(14,3) NOT NULL DEFAULT 0,
  value                 DECIMAL(16,2) NOT NULL DEFAULT 0,   -- tp_value x share
  net_value             DECIMAL(16,2) NOT NULL DEFAULT 0,
  discount_value        DECIMAL(16,2) NOT NULL DEFAULT 0,
  job_run_id            BIGINT UNSIGNED NULL,
  PRIMARY KEY (id, period_month),
  KEY ix_sa_terr (territory_id, period_month, product_id),
  KEY ix_sa_team (team_id, period_month),
  KEY ix_sa_txn (sales_transaction_id),
  KEY ix_sa_chem (chemist_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (period_month) (
  PARTITION p2024 VALUES LESS THAN (202501),
  PARTITION p2025 VALUES LESS THAN (202601),
  PARTITION p2026 VALUES LESS THAN (202701),
  PARTITION p2027 VALUES LESS THAN (202801),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 12.3 Daily roll-up feeding dashboards & daily/weekly trend reports.
--      Also receives pre-aggregated territory sales from Uploader/DailySale (source='upload').
CREATE TABLE sales_daily_summary (
  sale_date       DATE NOT NULL,
  period_month    INT UNSIGNED NOT NULL,
  territory_id    BIGINT UNSIGNED NOT NULL,
  product_id      BIGINT UNSIGNED NOT NULL,
  distributor_id  BIGINT UNSIGNED NOT NULL DEFAULT 0,   -- 0 = not distributor-specific (uploaded)
  source          ENUM('allocation','upload') NOT NULL DEFAULT 'allocation',
  team_id         BIGINT UNSIGNED NOT NULL,
  sale_units      DECIMAL(14,3) NOT NULL DEFAULT 0,
  sale_value      DECIMAL(16,2) NOT NULL DEFAULT 0,
  return_units    DECIMAL(14,3) NOT NULL DEFAULT 0,
  return_value    DECIMAL(16,2) NOT NULL DEFAULT 0,
  bonus_units     DECIMAL(14,3) NOT NULL DEFAULT 0,
  inst_units      DECIMAL(14,3) NOT NULL DEFAULT 0,
  inst_value      DECIMAL(16,2) NOT NULL DEFAULT 0,
  discount_value  DECIMAL(16,2) NOT NULL DEFAULT 0,
  net_value       DECIMAL(16,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (sale_date, territory_id, product_id, distributor_id, source, period_month),
  KEY ix_sds_team (team_id, sale_date),
  KEY ix_sds_terr (territory_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (period_month) (
  PARTITION p2024 VALUES LESS THAN (202501),
  PARTITION p2025 VALUES LESS THAN (202601),
  PARTITION p2026 VALUES LESS THAN (202701),
  PARTITION p2027 VALUES LESS THAN (202801),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 12.4 Monthly star-schema fact used by ~80% of sales reports (GOLM, GOLY, Ach%, pivots).
--      Also receives Uploader/MonthlySale rows (source='upload').
CREATE TABLE sales_monthly_summary (
  period_month    INT UNSIGNED NOT NULL,
  territory_id    BIGINT UNSIGNED NOT NULL,
  product_id      BIGINT UNSIGNED NOT NULL,
  distributor_id  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  channel         VARCHAR(30) NOT NULL DEFAULT '',     -- chemist type / channel
  source          ENUM('allocation','upload') NOT NULL DEFAULT 'allocation',
  team_id         BIGINT UNSIGNED NOT NULL,
  zone_id         BIGINT UNSIGNED NULL,
  region_id       BIGINT UNSIGNED NULL,
  brand_id        BIGINT UNSIGNED NULL,
  employee_id     BIGINT UNSIGNED NULL,
  sale_units      DECIMAL(14,3) NOT NULL DEFAULT 0,
  sale_value      DECIMAL(16,2) NOT NULL DEFAULT 0,
  return_units    DECIMAL(14,3) NOT NULL DEFAULT 0,
  return_value    DECIMAL(16,2) NOT NULL DEFAULT 0,
  bonus_units     DECIMAL(14,3) NOT NULL DEFAULT 0,
  bonus_value     DECIMAL(16,2) NOT NULL DEFAULT 0,
  inst_units      DECIMAL(14,3) NOT NULL DEFAULT 0,
  inst_value      DECIMAL(16,2) NOT NULL DEFAULT 0,
  discount_value  DECIMAL(16,2) NOT NULL DEFAULT 0,
  net_value       DECIMAL(16,2) NOT NULL DEFAULT 0,
  chemist_count   INT UNSIGNED NOT NULL DEFAULT 0,
  refreshed_at    DATETIME NULL,
  PRIMARY KEY (period_month, territory_id, product_id, distributor_id, channel, source),
  KEY ix_sms_team (team_id, period_month),
  KEY ix_sms_region (region_id, period_month),
  KEY ix_sms_brand (brand_id, period_month),
  KEY ix_sms_prod (product_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (period_month) (
  PARTITION p2024 VALUES LESS THAN (202501),
  PARTITION p2025 VALUES LESS THAN (202601),
  PARTITION p2026 VALUES LESS THAN (202701),
  PARTITION p2027 VALUES LESS THAN (202801),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 12.5 Targets (MREP: Uploader/UploadTargetsBulk, Reports/TargetReport, YearWiseTarget)
CREATE TABLE sales_targets (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  period_month          INT UNSIGNED NOT NULL,
  territory_id          BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED NOT NULL,
  target_units          DECIMAL(14,3) NOT NULL DEFAULT 0,   -- trade
  target_value          DECIMAL(16,2) NOT NULL DEFAULT 0,
  inst_target_units     DECIMAL(14,3) NOT NULL DEFAULT 0,   -- institution
  inst_target_value     DECIMAL(16,2) NOT NULL DEFAULT 0,
  import_batch_id       BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_tgt (period_month, territory_id, product_id),
  KEY ix_tgt_prod (product_id, period_month),
  CONSTRAINT fk_tgt_terr FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_tgt_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12.6 Distributor stock & batch position (MREP: Reports/StockReport)
CREATE TABLE distributor_stock (
  id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_month            INT UNSIGNED NOT NULL,
  stock_date              DATE NOT NULL,
  distributor_id          BIGINT UNSIGNED NOT NULL,
  product_id              BIGINT UNSIGNED NULL,
  dist_product_code       VARCHAR(50) NOT NULL,
  batch_no                VARCHAR(50) NULL,
  expiry_date             DATE NULL,
  opening_units           INT NOT NULL DEFAULT 0,
  opening_bonus           INT NOT NULL DEFAULT 0,
  purchase_units          INT NOT NULL DEFAULT 0,
  company_purchase_units  INT NOT NULL DEFAULT 0,
  purchase_bonus          INT NOT NULL DEFAULT 0,
  other_in                INT NOT NULL DEFAULT 0,
  other_out               INT NOT NULL DEFAULT 0,
  sale_units              INT NOT NULL DEFAULT 0,
  sale_bonus              INT NOT NULL DEFAULT 0,
  exports                 INT NOT NULL DEFAULT 0,
  damage_expiry           INT NOT NULL DEFAULT 0,
  closing_units           INT NOT NULL DEFAULT 0,
  closing_bonus           INT NOT NULL DEFAULT 0,
  stock_value             DECIMAL(16,2) NOT NULL DEFAULT 0,
  import_batch_id         BIGINT UNSIGNED NULL,
  PRIMARY KEY (id, period_month),
  KEY ix_ds (distributor_id, stock_date, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (period_month) (
  PARTITION p2025 VALUES LESS THAN (202601),
  PARTITION p2026 VALUES LESS THAN (202701),
  PARTITION p2027 VALUES LESS THAN (202801),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- =====================================================================
-- 13. FIELD ACTIVITY (SFE): work plans, calls, attendance, leave
-- =====================================================================

-- Event types drive call logic (doctor call, chemist call, meeting, training, leave, admin day, travel ...)
CREATE TABLE event_types (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code                VARCHAR(40)  NOT NULL UNIQUE,
  name                VARCHAR(100) NOT NULL,
  category            ENUM('customer_call','meeting','leave','admin','travel','training','other') NOT NULL,
  customer_type       ENUM('doctor','chemist','distributor','none') NOT NULL DEFAULT 'none',
  counts_as_field_day TINYINT(1) NOT NULL DEFAULT 0,
  requires_location   TINYINT(1) NOT NULL DEFAULT 0,
  is_active           TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plan rules per team/designation (MREP: CallEvent/GetWorkplanRules)
CREATE TABLE work_plan_rules (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id         BIGINT UNSIGNED NULL,
  designation_id  BIGINT UNSIGNED NULL,
  rule_key        VARCHAR(60) NOT NULL,     -- MAX_CALLS_PER_DAY, PLAN_SUBMIT_CUTOFF_DAY, BACKDATE_DAYS_ALLOWED, MIN_CALL_MINUTES ...
  rule_value      VARCHAR(100) NOT NULL,
  UNIQUE KEY uq_wpr (team_id, designation_id, rule_key),
  CONSTRAINT fk_wpr_team  FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_wpr_desig FOREIGN KEY (designation_id) REFERENCES designations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Monthly plan header (MREP: Callevent/Workplan, Reports/DoctorPlan "Planned Events")
CREATE TABLE work_plans (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id     BIGINT UNSIGNED NOT NULL,
  territory_id    BIGINT UNSIGNED NOT NULL,
  period_month    INT UNSIGNED NOT NULL,
  status          ENUM('draft','submitted','approved','rejected','revised') NOT NULL DEFAULT 'draft',
  submitted_at    DATETIME NULL,
  approved_at     DATETIME NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_wp (employee_id, territory_id, period_month),
  CONSTRAINT fk_wp_emp  FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_wp_terr FOREIGN KEY (territory_id) REFERENCES territories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE work_plan_items (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  work_plan_id          BIGINT UNSIGNED NOT NULL,
  plan_date             DATE NOT NULL,
  shift                 ENUM('morning','evening','full_day') NOT NULL DEFAULT 'morning',
  start_time            TIME NULL,
  end_time              TIME NULL,
  event_type_id         BIGINT UNSIGNED NOT NULL,
  doctor_id             BIGINT UNSIGNED NULL,
  practice_place_id     BIGINT UNSIGNED NULL,
  chemist_id            BIGINT UNSIGNED NULL,
  distributor_id        BIGINT UNSIGNED NULL,
  contact_point_id      BIGINT UNSIGNED NULL,
  from_town_id          BIGINT UNSIGNED NULL,
  to_town_id            BIGINT UNSIGNED NULL,
  accompanied_by_id     BIGINT UNSIGNED NULL,           -- planned joint visit
  notes                 VARCHAR(500) NULL,
  status                ENUM('planned','executed','missed','cancelled') NOT NULL DEFAULT 'planned',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_wpi_plan (work_plan_id, plan_date),
  KEY ix_wpi_doc (doctor_id, plan_date),
  CONSTRAINT fk_wpi_plan  FOREIGN KEY (work_plan_id) REFERENCES work_plans(id) ON DELETE CASCADE,
  CONSTRAINT fk_wpi_evt   FOREIGN KEY (event_type_id) REFERENCES event_types(id),
  CONSTRAINT fk_wpi_doc   FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_wpi_pp    FOREIGN KEY (practice_place_id) REFERENCES practice_places(id),
  CONSTRAINT fk_wpi_chem  FOREIGN KEY (chemist_id) REFERENCES chemists(id),
  CONSTRAINT fk_wpi_dist  FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_wpi_cp    FOREIGN KEY (contact_point_id) REFERENCES contact_points(id),
  CONSTRAINT fk_wpi_acc   FOREIGN KEY (accompanied_by_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE work_plan_item_products (
  work_plan_item_id        BIGINT UNSIGNED NOT NULL,
  product_id               BIGINT UNSIGNED NOT NULL,
  prescribing_response_id  BIGINT UNSIGNED NULL,        -- lookup PRODUCT PRESCRIPTION OPTION
  PRIMARY KEY (work_plan_item_id, product_id),
  CONSTRAINT fk_wpip_item FOREIGN KEY (work_plan_item_id) REFERENCES work_plan_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_wpip_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Back-dated event logging permission (MREP: Employee/EventLogPermission, EventPermissionLst)
CREATE TABLE event_log_permissions (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name               VARCHAR(150) NOT NULL,
  detail             VARCHAR(500) NULL,
  access_valid_from  DATETIME NOT NULL,                 -- when the employee may log
  access_valid_to    DATETIME NOT NULL,
  period_from        DATE NOT NULL,                     -- which past dates may be logged
  period_to          DATE NOT NULL,
  status             ENUM('pending','approved','rejected','expired') NOT NULL DEFAULT 'pending',
  created_by         BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_log_permission_employees (
  permission_id  BIGINT UNSIGNED NOT NULL,
  employee_id    BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (permission_id, employee_id),
  CONSTRAINT fk_elpe_perm FOREIGN KEY (permission_id) REFERENCES event_log_permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_elpe_emp  FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_log_permission_types (
  permission_id  BIGINT UNSIGNED NOT NULL,
  event_type_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (permission_id, event_type_id),
  CONSTRAINT fk_elpt_perm FOREIGN KEY (permission_id) REFERENCES event_log_permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_elpt_evt  FOREIGN KEY (event_type_id) REFERENCES event_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- E-detailing content (MREP: Callevent/ContentManagement)
CREATE TABLE presentations (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code           VARCHAR(30)  NOT NULL UNIQUE,
  name           VARCHAR(200) NOT NULL,
  team_id        BIGINT UNSIGNED NULL,
  brand_id       BIGINT UNSIGNED NULL,
  specialty_id   BIGINT UNSIGNED NULL,
  valid_from     DATE NOT NULL,
  valid_to       DATE NULL,
  status         ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_pres_team  FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_pres_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
  CONSTRAINT fk_pres_spec  FOREIGN KEY (specialty_id) REFERENCES specialties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE presentation_slides (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  presentation_id  BIGINT UNSIGNED NOT NULL,
  slide_no         SMALLINT UNSIGNED NOT NULL,
  file_path        VARCHAR(500) NOT NULL,
  mime_type        VARCHAR(100) NULL,
  UNIQUE KEY uq_ps (presentation_id, slide_no),
  CONSTRAINT fk_ps_pres FOREIGN KEY (presentation_id) REFERENCES presentations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Callevent/ProductQuestionnaire
CREATE TABLE questionnaire_questions (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT UNSIGNED NULL,
  statement     VARCHAR(500) NOT NULL,
  question_type ENUM('text','number','single_choice','multi_choice','yes_no','rating') NOT NULL,
  options       JSON NULL,
  valid_from    DATE NOT NULL,
  valid_to      DATE NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_qq_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Executed call / event (the core SFE fact; synced from mobile app)
-- MREP: Reports/CallLog, CallLogV4, PlannedEvents, AccompaniedByCallLog, Map/DoctorCalls
CREATE TABLE call_events (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_uuid          CHAR(36) NOT NULL UNIQUE,         -- idempotent offline sync key
  employee_id          BIGINT UNSIGNED NOT NULL,
  territory_id         BIGINT UNSIGNED NOT NULL,
  team_id              BIGINT UNSIGNED NOT NULL,
  work_plan_item_id    BIGINT UNSIGNED NULL,             -- NULL = unplanned
  event_type_id        BIGINT UNSIGNED NOT NULL,
  meeting_type_id      BIGINT UNSIGNED NULL,             -- lookup MEETING TYPE
  shift                ENUM('morning','evening','full_day') NOT NULL,
  call_date            DATE NOT NULL,
  started_at           DATETIME NOT NULL,
  ended_at             DATETIME NULL,
  duration_sec         INT UNSIGNED GENERATED ALWAYS AS (TIMESTAMPDIFF(SECOND, started_at, ended_at)) STORED,
  synced_at            DATETIME NULL,
  is_planned           TINYINT(1) NOT NULL DEFAULT 0,
  call_status          ENUM('happened','not_met','cancelled') NOT NULL DEFAULT 'happened',
  not_met_reason_id    BIGINT UNSIGNED NULL,
  doctor_id            BIGINT UNSIGNED NULL,
  practice_place_id    BIGINT UNSIGNED NULL,
  chemist_id           BIGINT UNSIGNED NULL,
  distributor_id       BIGINT UNSIGNED NULL,
  contact_point_id     BIGINT UNSIGNED NULL,
  from_city_id         BIGINT UNSIGNED NULL,
  to_city_id           BIGINT UNSIGNED NULL,
  latitude             DECIMAL(10,7) NULL,
  longitude            DECIMAL(10,7) NULL,
  gps_accuracy_m       DECIMAL(8,2) NULL,
  distance_m           DECIMAL(10,2) NULL,               -- from practice place
  location_status      ENUM('on','near','off','not_tagged','na') NOT NULL DEFAULT 'na',
  is_cp_local          TINYINT(1) NULL,                  -- Local / OutStation CP status
  is_on_time           TINYINT(1) NULL,                  -- OnTime / Late
  is_backdated         TINYINT(1) NOT NULL DEFAULT 0,
  event_permission_id  BIGINT UNSIGNED NULL,
  notes                VARCHAR(1000) NULL,
  comments             VARCHAR(1000) NULL,
  description          VARCHAR(1000) NULL,
  device_id            VARCHAR(100) NULL,
  app_version          VARCHAR(30) NULL,
  approval_status      ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_ce_emp_date (employee_id, call_date),
  KEY ix_ce_terr_date (territory_id, call_date),
  KEY ix_ce_team_date (team_id, call_date),
  KEY ix_ce_doc (doctor_id, call_date),
  KEY ix_ce_chem (chemist_id, call_date),
  KEY ix_ce_date (call_date),
  CONSTRAINT fk_ce_emp   FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_ce_terr  FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_ce_team  FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_ce_wpi   FOREIGN KEY (work_plan_item_id) REFERENCES work_plan_items(id),
  CONSTRAINT fk_ce_evt   FOREIGN KEY (event_type_id) REFERENCES event_types(id),
  CONSTRAINT fk_ce_mt    FOREIGN KEY (meeting_type_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_ce_nmr   FOREIGN KEY (not_met_reason_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_ce_doc   FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_ce_pp    FOREIGN KEY (practice_place_id) REFERENCES practice_places(id),
  CONSTRAINT fk_ce_chem  FOREIGN KEY (chemist_id) REFERENCES chemists(id),
  CONSTRAINT fk_ce_dist  FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_ce_cp    FOREIGN KEY (contact_point_id) REFERENCES contact_points(id),
  CONSTRAINT fk_ce_perm  FOREIGN KEY (event_permission_id) REFERENCES event_log_permissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Joint working / accompanied by (MREP: Joint Working Report, Joint Calls by Team/Manager)
CREATE TABLE call_event_participants (
  call_event_id    BIGINT UNSIGNED NOT NULL,
  employee_id      BIGINT UNSIGNED NOT NULL,
  designation_id   BIGINT UNSIGNED NULL,
  PRIMARY KEY (call_event_id, employee_id),
  KEY ix_cep_emp (employee_id),
  CONSTRAINT fk_cep_ce  FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_cep_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products detailed in the call (MREP: Employee Product Calls, Top 5 Product/Brand calls)
CREATE TABLE call_event_products (
  call_event_id            BIGINT UNSIGNED NOT NULL,
  product_id               BIGINT UNSIGNED NOT NULL,
  sequence_no              TINYINT UNSIGNED NOT NULL DEFAULT 1,
  is_focus                 TINYINT(1) NOT NULL DEFAULT 0,
  prescribing_response_id  BIGINT UNSIGNED NULL,
  PRIMARY KEY (call_event_id, product_id),
  KEY ix_cepr_prod (product_id),
  CONSTRAINT fk_cepr_ce   FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_cepr_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Samples & gifts handed over in the call (also written to promo_stock_movements)
CREATE TABLE call_event_promo_items (
  call_event_id  BIGINT UNSIGNED NOT NULL,
  promo_item_id  BIGINT UNSIGNED NOT NULL,
  quantity       INT UNSIGNED NOT NULL,
  PRIMARY KEY (call_event_id, promo_item_id),
  CONSTRAINT fk_cepi_ce   FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_cepi_item FOREIGN KEY (promo_item_id) REFERENCES promo_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chemist call: availability / order booking / sale trend
CREATE TABLE call_event_orders (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  call_event_id   BIGINT UNSIGNED NOT NULL,
  product_id      BIGINT UNSIGNED NOT NULL,
  is_available    TINYINT(1) NULL,
  order_quantity  INT UNSIGNED NULL,
  sold_packs      INT UNSIGNED NULL,
  sale_trend      ENUM('up','same','down') NULL,
  booker_name     VARCHAR(100) NULL,
  KEY ix_ceo_ce (call_event_id),
  CONSTRAINT fk_ceo_ce   FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_ceo_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- E-detailing slide time (MREP: Call & EDA's Summary)
CREATE TABLE call_event_slide_views (
  call_event_id    BIGINT UNSIGNED NOT NULL,
  presentation_id  BIGINT UNSIGNED NOT NULL,
  slide_no         SMALLINT UNSIGNED NOT NULL,
  seconds_viewed   INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (call_event_id, presentation_id, slide_no),
  CONSTRAINT fk_cesv_ce   FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_cesv_pres FOREIGN KEY (presentation_id) REFERENCES presentations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE call_event_answers (
  call_event_id  BIGINT UNSIGNED NOT NULL,
  question_id    BIGINT UNSIGNED NOT NULL,
  answer         VARCHAR(1000) NULL,
  PRIMARY KEY (call_event_id, question_id),
  CONSTRAINT fk_cea_ce FOREIGN KEY (call_event_id) REFERENCES call_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_cea_q  FOREIGN KEY (question_id) REFERENCES questionnaire_questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daily attendance (MREP: Reports/MarkedAttendence, EmployeeAttendence[Monthly])
CREATE TABLE attendance (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id        BIGINT UNSIGNED NOT NULL,
  attendance_date    DATE NOT NULL,
  status             ENUM('present','absent','leave','vacation','holiday','weekend') NOT NULL,
  marked_at          DATETIME NULL,
  synced_at          DATETIME NULL,
  meeting_type_id    BIGINT UNSIGNED NULL,
  contact_point_id   BIGINT UNSIGNED NULL,
  latitude           DECIMAL(10,7) NULL,
  longitude          DECIMAL(10,7) NULL,
  distance_km        DECIMAL(8,2) NULL,
  visit_category     ENUM('local','outstation','ex_station','na') NOT NULL DEFAULT 'na',
  source             ENUM('mobile','web','system') NOT NULL DEFAULT 'mobile',
  UNIQUE KEY uq_att (employee_id, attendance_date),
  KEY ix_att_date (attendance_date),
  CONSTRAINT fk_att_emp FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_att_cp  FOREIGN KEY (contact_point_id) REFERENCES contact_points(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Reports/LeaveReport
CREATE TABLE leaves (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id     BIGINT UNSIGNED NOT NULL,
  leave_type_id   BIGINT UNSIGNED NOT NULL,            -- lookup LEAVE TYPE
  from_date       DATE NOT NULL,
  to_date         DATE NOT NULL,
  days            DECIMAL(4,1) NOT NULL,
  reason          VARCHAR(500) NULL,
  status          ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_leave_emp (employee_id, from_date),
  CONSTRAINT fk_leave_emp  FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_leave_type FOREIGN KEY (leave_type_id) REFERENCES lookup_values(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pre-aggregated KPIs per employee-day (Calls dashboard, Employee KPI, Avg/Day, Call Performance)
CREATE TABLE call_daily_summary (
  employee_id        BIGINT UNSIGNED NOT NULL,
  call_date          DATE NOT NULL,
  territory_id       BIGINT UNSIGNED NOT NULL,
  team_id            BIGINT UNSIGNED NOT NULL,
  planned_calls      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  planned_done       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  unplanned_done     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  not_met            SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  morning_calls      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  evening_calls      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  doctor_calls       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  chemist_calls      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  joint_calls        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  on_location        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  near_location      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  off_location       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  samples_issued     INT UNSIGNED NOT NULL DEFAULT 0,
  is_field_day       TINYINT(1) NOT NULL DEFAULT 0,
  refreshed_at       DATETIME NULL,
  PRIMARY KEY (employee_id, call_date),
  KEY ix_cds_terr (territory_id, call_date),
  KEY ix_cds_team (team_id, call_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Monthly coverage per territory x grade (Doctor Coverage, RFI, Visit Deviation)
CREATE TABLE doctor_coverage_monthly (
  period_month       INT UNSIGNED NOT NULL,
  territory_id       BIGINT UNSIGNED NOT NULL,
  grade_name         VARCHAR(20) NOT NULL DEFAULT '',
  target_doctors     INT UNSIGNED NOT NULL DEFAULT 0,
  planned_doctors    INT UNSIGNED NOT NULL DEFAULT 0,
  covered_doctors    INT UNSIGNED NOT NULL DEFAULT 0,
  unplanned_doctors  INT UNSIGNED NOT NULL DEFAULT 0,
  required_visits    INT UNSIGNED NOT NULL DEFAULT 0,
  actual_visits      INT UNSIGNED NOT NULL DEFAULT 0,
  refreshed_at       DATETIME NULL,
  PRIMARY KEY (period_month, territory_id, grade_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 14. EXPENSES  (MREP: Expense/*)
-- =====================================================================

-- Expense heads (MREP: Expense/Expense: GL Code, Name, Measurement Unit, Max Limit Criteria, Category)
CREATE TABLE expense_heads (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gl_code             VARCHAR(30)  NOT NULL UNIQUE,
  name                VARCHAR(150) NOT NULL,
  category_id         BIGINT UNSIGNED NULL,             -- lookup EXPENSE CATEGORY
  head_type           ENUM('fixed','daily_allowance','mileage','night_stay','ex_station','other') NOT NULL,
  measurement_unit    ENUM('per_day','per_km','per_night','per_month','lumpsum','per_unit') NOT NULL,
  max_limit_criteria  ENUM('per_day','per_month','per_claim','none') NOT NULL DEFAULT 'none',
  requires_attachment TINYINT(1) NOT NULL DEFAULT 0,
  is_active           TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  CONSTRAINT fk_eh_cat FOREIGN KEY (category_id) REFERENCES lookup_values(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate card per grade (MREP: Expense/GradeExpense incl. "Replicate month")
CREATE TABLE grade_expense_rates (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expense_grade_id   BIGINT UNSIGNED NOT NULL,
  expense_head_id    BIGINT UNSIGNED NOT NULL,
  period_month       INT UNSIGNED NOT NULL,             -- rates are versioned per month
  allowance_type     ENUM('local','outstation','big_city','outskirt','any') NOT NULL DEFAULT 'any',
  rate_per_unit      DECIMAL(12,2) NOT NULL,
  max_allowed        DECIMAL(12,2) NULL,
  no_ftf_max         DECIMAL(12,2) NULL,                -- max when no face-to-face calls
  weekend_max        DECIMAL(12,2) NULL,
  is_active          TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_ger (expense_grade_id, expense_head_id, period_month, allowance_type),
  CONSTRAINT fk_ger_grade FOREIGN KEY (expense_grade_id) REFERENCES expense_grades(id),
  CONSTRAINT fk_ger_head  FOREIGN KEY (expense_head_id) REFERENCES expense_heads(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee specific towns eligible for local / outskirt allowance
CREATE TABLE employee_expense_towns (
  employee_id  BIGINT UNSIGNED NOT NULL,
  town_id      BIGINT UNSIGNED NOT NULL,
  town_class   ENUM('base','outskirt','outstation') NOT NULL DEFAULT 'base',
  PRIMARY KEY (employee_id, town_id),
  CONSTRAINT fk_eet_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_eet_town FOREIGN KEY (town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE expense_claims (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  claim_no            VARCHAR(30) NOT NULL UNIQUE,
  employee_id         BIGINT UNSIGNED NOT NULL,
  territory_id        BIGINT UNSIGNED NULL,
  expense_grade_id    BIGINT UNSIGNED NULL,
  period_month        INT UNSIGNED NOT NULL,
  start_date          DATE NOT NULL,
  end_date            DATE NOT NULL,
  status              ENUM('draft','submitted','in_approval','approved','rejected','returned','paid') NOT NULL DEFAULT 'draft',
  current_level       TINYINT UNSIGNED NOT NULL DEFAULT 0,
  claimed_amount      DECIMAL(14,2) NOT NULL DEFAULT 0,
  approved_amount     DECIMAL(14,2) NOT NULL DEFAULT 0,
  deduction_amount    DECIMAL(14,2) NOT NULL DEFAULT 0,
  adjustment_amount   DECIMAL(14,2) NOT NULL DEFAULT 0,
  adjustment_reason   VARCHAR(255) NULL,
  payable_amount      DECIMAL(14,2) GENERATED ALWAYS AS (approved_amount - deduction_amount + adjustment_amount) STORED,
  comments            VARCHAR(1000) NULL,
  submitted_at        DATETIME NULL,
  final_approved_at   DATETIME NULL,
  paid_at             DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_ec_emp (employee_id, period_month),
  KEY ix_ec_status (status, period_month),
  CONSTRAINT fk_ec_emp   FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_ec_terr  FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_ec_grade FOREIGN KEY (expense_grade_id) REFERENCES expense_grades(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE expense_claim_lines (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expense_claim_id     BIGINT UNSIGNED NOT NULL,
  expense_date         DATE NOT NULL,
  expense_head_id      BIGINT UNSIGNED NOT NULL,
  working_type         ENUM('local','outstation') NOT NULL DEFAULT 'local',
  origin_town_id       BIGINT UNSIGNED NULL,
  destination_town_id  BIGINT UNSIGNED NULL,
  stop_index           TINYINT UNSIGNED NULL,           -- multi-stop travel ("Visit Index")
  stop_type            VARCHAR(20) NULL,
  distance_km          DECIMAL(8,2) NULL,
  is_night_stay        TINYINT(1) NOT NULL DEFAULT 0,
  hotel_name           VARCHAR(150) NULL,
  units                DECIMAL(10,2) NOT NULL DEFAULT 1,
  rate                 DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_allowed          DECIMAL(12,2) NULL,
  claimed_amount       DECIMAL(12,2) NOT NULL,
  approved_units       DECIMAL(10,2) NULL,
  approved_amount      DECIMAL(12,2) NULL,
  changed_by           BIGINT UNSIGNED NULL,           -- approver who edited units/amount
  status               ENUM('pending','approved','rejected','adjusted') NOT NULL DEFAULT 'pending',
  notes                VARCHAR(500) NULL,
  KEY ix_ecl_claim (expense_claim_id, expense_date),
  CONSTRAINT fk_ecl_claim FOREIGN KEY (expense_claim_id) REFERENCES expense_claims(id) ON DELETE CASCADE,
  CONSTRAINT fk_ecl_head  FOREIGN KEY (expense_head_id) REFERENCES expense_heads(id),
  CONSTRAINT fk_ecl_orig  FOREIGN KEY (origin_town_id) REFERENCES towns(id),
  CONSTRAINT fk_ecl_dest  FOREIGN KEY (destination_town_id) REFERENCES towns(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Calls that justify an expense line (approver sees Dr / location / ON-OFF status per line)
CREATE TABLE expense_claim_line_calls (
  expense_claim_line_id  BIGINT UNSIGNED NOT NULL,
  call_event_id          BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (expense_claim_line_id, call_event_id),
  CONSTRAINT fk_eclc_line FOREIGN KEY (expense_claim_line_id) REFERENCES expense_claim_lines(id) ON DELETE CASCADE,
  CONSTRAINT fk_eclc_ce   FOREIGN KEY (call_event_id) REFERENCES call_events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MREP: Expense/ExpenseDeduction, ExpenseDeductionSummary
CREATE TABLE expense_deductions (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expense_claim_id  BIGINT UNSIGNED NOT NULL,
  deduction_date    DATE NOT NULL,
  amount            DECIMAL(12,2) NOT NULL,
  reason            VARCHAR(500) NOT NULL,
  created_by        BIGINT UNSIGNED NULL,
  created_at        TIMESTAMP NULL,
  CONSTRAINT fk_ed_claim FOREIGN KEY (expense_claim_id) REFERENCES expense_claims(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 15. OBLIGATIONS / PATIENT-CARE / CE & ROI  (MREP: Obligation/*, ROI/*, P.Care Dashboard)
-- =====================================================================

CREATE TABLE obligation_budgets (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id       BIGINT UNSIGNED NOT NULL,
  territory_id  BIGINT UNSIGNED NULL,                   -- NULL = team-level budget
  fiscal_year   SMALLINT UNSIGNED NOT NULL,
  quarter       TINYINT UNSIGNED NULL,
  amount        DECIMAL(16,2) NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  UNIQUE KEY uq_ob (team_id, territory_id, fiscal_year, quarter),
  CONSTRAINT fk_ob_team FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_ob_terr FOREIGN KEY (territory_id) REFERENCES territories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE obligations (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligation_no          VARCHAR(30) NOT NULL UNIQUE,   -- "Activity No." / ROI Code
  care_plan_type_id      BIGINT UNSIGNED NULL,          -- lookup CARE PLAN TYPE
  obligation_type_id     BIGINT UNSIGNED NOT NULL,      -- lookup OBLIGATION TYPE (CE Type)
  roi_type_id            BIGINT UNSIGNED NULL,          -- lookup ROI TYPES
  business_objective_id  BIGINT UNSIGNED NULL,          -- lookup OBLIGATION BUSINESS OBJECTIVES
  payment_mode_id        BIGINT UNSIGNED NULL,          -- lookup CASH TYPES / PAYMENT MODE
  duration_type_id       BIGINT UNSIGNED NULL,          -- lookup DURATION TYPE (one-time / monthly ...)
  employee_id            BIGINT UNSIGNED NOT NULL,
  territory_id           BIGINT UNSIGNED NOT NULL,
  team_id                BIGINT UNSIGNED NOT NULL,
  doctor_id              BIGINT UNSIGNED NOT NULL,
  practice_place_id      BIGINT UNSIGNED NULL,
  distributor_id         BIGINT UNSIGNED NULL,
  budget_id              BIGINT UNSIGNED NULL,
  expected_date          DATE NULL,
  from_month             INT UNSIGNED NOT NULL,
  to_month               INT UNSIGNED NOT NULL,
  committed_months       TINYINT UNSIGNED NULL,
  requested_amount       DECIMAL(14,2) NOT NULL,
  approved_amount        DECIMAL(14,2) NULL,
  paid_amount            DECIMAL(14,2) NULL,
  cp_actual_market_share DECIMAL(6,2) NULL,
  cp_market_share        DECIMAL(6,2) NULL,
  cp_class_volume        DECIMAL(14,2) NULL,
  se_ratio               DECIMAL(8,2) NULL,             -- sales / expense ratio
  cheque_ref_no          VARCHAR(60) NULL,
  request_notes          VARCHAR(1000) NULL,
  remarks                VARCHAR(1000) NULL,
  admin_ref_no           VARCHAR(60) NULL,
  admin_remarks          VARCHAR(1000) NULL,
  status                 ENUM('draft','submitted','in_approval','approved','rejected','paid','closed','cancelled')
                         NOT NULL DEFAULT 'submitted',
  current_level          TINYINT UNSIGNED NOT NULL DEFAULT 1,
  received_date          DATE NULL,
  paid_date              DATE NULL,
  roi_status             ENUM('pending','on_track','achieved','not_achieved') NOT NULL DEFAULT 'pending',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL,
  KEY ix_obl_emp (employee_id, from_month),
  KEY ix_obl_doc (doctor_id),
  KEY ix_obl_status (status, team_id),
  CONSTRAINT fk_obl_type   FOREIGN KEY (obligation_type_id) REFERENCES lookup_values(id),
  CONSTRAINT fk_obl_emp    FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_obl_terr   FOREIGN KEY (territory_id) REFERENCES territories(id),
  CONSTRAINT fk_obl_team   FOREIGN KEY (team_id) REFERENCES teams(id),
  CONSTRAINT fk_obl_doc    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  CONSTRAINT fk_obl_pp     FOREIGN KEY (practice_place_id) REFERENCES practice_places(id),
  CONSTRAINT fk_obl_dist   FOREIGN KEY (distributor_id) REFERENCES distributors(id),
  CONSTRAINT fk_obl_budget FOREIGN KEY (budget_id) REFERENCES obligation_budgets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Committed products with baseline & expected growth
CREATE TABLE obligation_products (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligation_id          BIGINT UNSIGNED NOT NULL,
  product_id             BIGINT UNSIGNED NOT NULL,
  baseline_units_3m      DECIMAL(14,2) NULL,            -- last 3 months avg units
  current_units          DECIMAL(14,2) NULL,
  committed_units_month  DECIMAL(14,2) NOT NULL,
  expected_growth_pct    DECIMAL(6,2) NULL,
  order_quantity         INT UNSIGNED NULL,
  free_goods             INT UNSIGNED NULL,
  UNIQUE KEY uq_op (obligation_id, product_id),
  CONSTRAINT fk_op_obl  FOREIGN KEY (obligation_id) REFERENCES obligations(id) ON DELETE CASCADE,
  CONSTRAINT fk_op_prod FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chemists whose sales prove the ROI (prescription share)
CREATE TABLE obligation_chemists (
  obligation_id  BIGINT UNSIGNED NOT NULL,
  chemist_id     BIGINT UNSIGNED NOT NULL,
  share_pct      DECIMAL(6,2) NOT NULL DEFAULT 100,
  PRIMARY KEY (obligation_id, chemist_id),
  CONSTRAINT fk_oc_obl  FOREIGN KEY (obligation_id) REFERENCES obligations(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_chem FOREIGN KEY (chemist_id) REFERENCES chemists(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE obligation_competitors (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligation_id          BIGINT UNSIGNED NOT NULL,
  competitor_product_id  BIGINT UNSIGNED NULL,
  competitor_name        VARCHAR(200) NULL,
  company_name           VARCHAR(150) NULL,
  monthly_units          INT UNSIGNED NULL,
  CONSTRAINT fk_ocomp_obl FOREIGN KEY (obligation_id) REFERENCES obligations(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocomp_cp  FOREIGN KEY (competitor_product_id) REFERENCES competitor_products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Post-activity sales tracking (Sales AOM-1..12, ROI Achieved %) - refreshed by ROI job
CREATE TABLE obligation_roi_monthly (
  obligation_id  BIGINT UNSIGNED NOT NULL,
  period_month   INT UNSIGNED NOT NULL,
  product_id     BIGINT UNSIGNED NOT NULL,
  month_index    TINYINT NOT NULL,                      -- -3..-1 baseline, 1..12 after activity
  sale_units     DECIMAL(14,3) NOT NULL DEFAULT 0,
  sale_value     DECIMAL(16,2) NOT NULL DEFAULT 0,
  refreshed_at   DATETIME NULL,
  PRIMARY KEY (obligation_id, period_month, product_id),
  CONSTRAINT fk_orm_obl FOREIGN KEY (obligation_id) REFERENCES obligations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- 16. REPORTING VIEWS  (thin, stable contracts for the report layer)
-- =====================================================================

-- Current incumbent per territory
CREATE OR REPLACE VIEW v_territory_current AS
SELECT t.id AS territory_id, t.code AS territory_code, t.team_id, tm.name AS team_name,
       r.zone_id, z.name AS zone_name, t.region_id, r.name AS region_name,
       t.designation_id, d.code AS designation_code, t.parent_id,
       e.id AS employee_id, e.code AS employee_code, e.full_name AS employee_name,
       t.base_town_id, t.is_vacant, t.is_active
FROM territories t
JOIN teams tm         ON tm.id = t.team_id
JOIN designations d   ON d.id  = t.designation_id
LEFT JOIN regions r   ON r.id  = t.region_id
LEFT JOIN zones z     ON z.id  = r.zone_id
LEFT JOIN territory_assignments ta ON ta.territory_id = t.id AND ta.is_primary = 1 AND ta.to_date IS NULL
LEFT JOIN employees e ON e.id = ta.employee_id;

-- Monthly sales vs target with growth bases (feeds Target vs Achievement, Territory Wise Sale, dashboards)
CREATE OR REPLACE VIEW v_sales_vs_target AS
SELECT k.period_month, k.territory_id, k.product_id,
       COALESCE(s.sale_units,0)  AS sale_units,
       COALESCE(s.sale_value,0)  AS sale_value,
       COALESCE(t.target_units,0) AS target_units,
       COALESCE(t.target_value,0) AS target_value,
       COALESCE(t.inst_target_units,0) AS inst_target_units,
       COALESCE(t.inst_target_value,0) AS inst_target_value
FROM (
  SELECT period_month, territory_id, product_id FROM sales_monthly_summary
  UNION
  SELECT period_month, territory_id, product_id FROM sales_targets
) k
LEFT JOIN (
  SELECT period_month, territory_id, product_id, SUM(sale_units) sale_units, SUM(sale_value) sale_value
  FROM sales_monthly_summary GROUP BY period_month, territory_id, product_id
) s ON s.period_month = k.period_month AND s.territory_id = k.territory_id AND s.product_id = k.product_id
LEFT JOIN sales_targets t
  ON t.period_month = k.period_month AND t.territory_id = k.territory_id AND t.product_id = k.product_id;