-- ============================================================================
-- PharmaSFA Pro — Complete MySQL Database Schema
-- Database: MSfa_db
-- Engine:   MySQL 8.x (compatible with MariaDB 10.4+ on shared hosting)
-- Charset:  utf8mb4 (full Unicode + emoji support)
-- Source:   Pharmasfa Pro PRD v1.0
--
-- HOW TO IMPORT ON SHARED HOSTING (cPanel):
--   1. cPanel → MySQL Database Wizard
--        DB name:     youruser_MSfa_db   (cPanel prefixes your username)
--        DB user:     pharma_admin       (or youruser_pharma_adm)
--        Privileges:  ALL PRIVILEGES
--   2. cPanel → phpMyAdmin → select MSfa_db → Import tab
--        Choose File: this schema.sql
--        Format:      SQL
--        Character set: utf-8
--        Click "Go"  →  all tables + seed data created
--   3. Update your app's .env with:
--        DATABASE_URL="mysql://pharma_admin:PASSWORD@localhost:3306/MSfa_db"
--
-- NOTES:
--   * If your cPanel username is "myco", the actual DB name becomes "myco_MSfa_db".
--     Either rename the CREATE DATABASE line below, or create the DB manually in
--     cPanel first and remove the CREATE DATABASE / USE statements.
--   * All tables use InnoDB (FK + transaction safe) and utf8mb4_unicode_ci.
--   * Foreign keys enforce referential integrity. Drop order is reversed.
--   * Demo seed data is included at the end so the app shows records immediately.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 0. DATABASE + CONNECTION
-- ----------------------------------------------------------------------------
DROP DATABASE IF EXISTS MSfa_db;
CREATE DATABASE IF NOT EXISTS MSfa_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE MSfa_db;

-- Pragmas for shared hosting (safe to ignore on import)
SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET time_zone = '+00:00';

-- ============================================================================
-- 1. AUTHENTICATION MODULE (PRD §4)
--    Tables: users, roles, permissions, user_roles, login_history, otp_logs, devices
-- ============================================================================
CREATE TABLE IF NOT EXISTS roles (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(40)  NOT NULL UNIQUE,
  description   VARCHAR(200),
  is_system     TINYINT(1)   NOT NULL DEFAULT 0,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(80)  NOT NULL UNIQUE,
  module        VARCHAR(60)  NOT NULL,
  description   VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  username        VARCHAR(60)  NOT NULL UNIQUE,
  email           VARCHAR(120) NOT NULL UNIQUE,
  mobile          VARCHAR(20)  NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  mfa_secret      VARCHAR(64),
  mfa_enabled     TINYINT(1)   NOT NULL DEFAULT 0,
  status          ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  device_id       VARCHAR(160),
  last_login_at   DATETIME,
  failed_attempts INT          NOT NULL DEFAULT 0,
  locked_until    DATETIME,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_status (status),
  INDEX idx_users_mobile (mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS login_history (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT,
  login_at      DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_address    VARCHAR(45),
  user_agent    VARCHAR(255),
  device_id     VARCHAR(160),
  session_token VARCHAR(255),
  status        ENUM('Success','Failed') NOT NULL DEFAULT 'Success',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_login_user (user_id),
  INDEX idx_login_time (login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS otp_logs (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT       NOT NULL,
  otp_code    VARCHAR(10)  NOT NULL,
  channel     ENUM('SMS','Email','TOTP') NOT NULL DEFAULT 'SMS',
  expires_at  DATETIME     NOT NULL,
  verified    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_otp_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id        BIGINT       NOT NULL,
  device_id      VARCHAR(160) NOT NULL,
  device_name    VARCHAR(120),
  platform       VARCHAR(40),
  app_version    VARCHAR(20),
  last_seen_at   DATETIME,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_device (user_id, device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 2. TERRITORY MODULE (PRD §5)
--    Hierarchy: Zone → Region → Area → Territory → HQ → Beat
-- ============================================================================
CREATE TABLE IF NOT EXISTS zones (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(10) NOT NULL UNIQUE,
  name        VARCHAR(80) NOT NULL,
  manager     VARCHAR(120),
  status      ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS regions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(10) NOT NULL UNIQUE,
  name        VARCHAR(80) NOT NULL,
  zone_id     INT NOT NULL,
  manager     VARCHAR(120),
  status      ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE RESTRICT,
  INDEX idx_region_zone (zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS areas (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(10) NOT NULL UNIQUE,
  name        VARCHAR(80) NOT NULL,
  region_id   INT NOT NULL,
  manager     VARCHAR(120),
  status      ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE RESTRICT,
  INDEX idx_area_region (region_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS territories (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(20) NOT NULL UNIQUE,
  name        VARCHAR(80) NOT NULL,
  area_id     INT NOT NULL,
  hq          VARCHAR(80),
  status      ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE RESTRICT,
  INDEX idx_terr_area (area_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hq_master (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(20) NOT NULL UNIQUE,
  name        VARCHAR(80) NOT NULL,
  territory_id INT,
  status      ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS beats (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  code         VARCHAR(20) NOT NULL UNIQUE,
  name         VARCHAR(80) NOT NULL,
  territory_id INT NOT NULL,
  visit_day    ENUM('Mon','Tue','Wed','Thu','Fri','Sat','Sun'),
  status       ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
  INDEX idx_beat_terr (territory_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 3. EMPLOYEE MODULE (PRD §6)
-- ============================================================================
CREATE TABLE IF NOT EXISTS employees (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  emp_code      VARCHAR(30)  NOT NULL UNIQUE,
  name          VARCHAR(120) NOT NULL,
  designation   VARCHAR(80)  NOT NULL,
  department    VARCHAR(40)  NOT NULL DEFAULT 'Sales',
  email         VARCHAR(120) UNIQUE,
  mobile        VARCHAR(20)  UNIQUE,
  doj           DATE,
  manager_id    BIGINT,
  territory_id  INT,
  hq            VARCHAR(80),
  status        ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  photo_url     VARCHAR(255),
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (manager_id)   REFERENCES employees(id) ON DELETE SET NULL,
  FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
  INDEX idx_emp_code (emp_code),
  INDEX idx_emp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_documents (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id  BIGINT NOT NULL,
  doc_type     VARCHAR(60),
  file_name    VARCHAR(255),
  file_url     VARCHAR(255),
  uploaded_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Link users ↔ employees (a login account maps to exactly one employee record)
ALTER TABLE users
  ADD COLUMN employee_id BIGINT AFTER device_id,
  ADD FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
  ADD INDEX idx_users_emp (employee_id);

-- ============================================================================
-- 4. DOCTOR MASTER (PRD §7)
-- ============================================================================
CREATE TABLE IF NOT EXISTS doctor_categories (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  code  VARCHAR(4)  NOT NULL UNIQUE,
  name  VARCHAR(40),
  weight INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS doctor_specialties (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS doctor_frequency (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS doctors (
  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
  doctor_code         VARCHAR(30)  NOT NULL UNIQUE,
  name                VARCHAR(120) NOT NULL,
  qualification       VARCHAR(80),
  specialty_id        INT,
  category_id         INT,
  hospital_id         BIGINT,
  area                VARCHAR(80),
  mobile              VARCHAR(20),
  email               VARCHAR(120),
  gps_lat             DECIMAL(10,7),
  gps_lng             DECIMAL(10,7),
  potential_score     TINYINT UNSIGNED DEFAULT 0,
  frequency_id        INT,
  rx_potential        ENUM('High','Medium','Low') DEFAULT 'Medium',
  status              ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (specialty_id) REFERENCES doctor_specialties(id) ON DELETE SET NULL,
  FOREIGN KEY (category_id)  REFERENCES doctor_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (frequency_id) REFERENCES doctor_frequency(id)  ON DELETE SET NULL,
  FOREIGN KEY (hospital_id)  REFERENCES hospitals(id)         ON DELETE SET NULL,
  INDEX idx_doc_status (status),
  INDEX idx_doc_cat (category_id),
  INDEX idx_doc_spec (specialty_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- NOTE: hospitals table is defined in §6 below; FK above resolves after that
--       table exists (MySQL allows forward references only if both are in the
--       same import run, which they are).

-- ============================================================================
-- 5. CHEMIST MASTER (PRD §8)
-- ============================================================================
CREATE TABLE IF NOT EXISTS chemists (
  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
  chemist_code   VARCHAR(30)  NOT NULL UNIQUE,
  name           VARCHAR(120) NOT NULL,
  type           ENUM('Retail','Wholesale','Hospital') NOT NULL DEFAULT 'Retail',
  contact_person VARCHAR(120),
  mobile         VARCHAR(20),
  email          VARCHAR(120),
  address        VARCHAR(255),
  territory_id   INT,
  status         ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE SET NULL,
  INDEX idx_chm_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chemist_contacts (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  chemist_id  BIGINT NOT NULL,
  name        VARCHAR(120),
  mobile      VARCHAR(20),
  email       VARCHAR(120),
  role        VARCHAR(60),
  FOREIGN KEY (chemist_id) REFERENCES chemists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 6. HOSPITAL MASTER (PRD §9)
-- ============================================================================
CREATE TABLE IF NOT EXISTS hospital_types (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hospitals (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  hospital_code   VARCHAR(30)  NOT NULL UNIQUE,
  name            VARCHAR(120) NOT NULL,
  type_id         INT,
  category        ENUM('A','B','C') NOT NULL DEFAULT 'B',
  address         VARCHAR(255),
  territory_id    INT,
  capacity        INT NOT NULL DEFAULT 0,
  status          ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (type_id)      REFERENCES hospital_types(id) ON DELETE SET NULL,
  FOREIGN KEY (territory_id) REFERENCES territories(id)    ON DELETE SET NULL,
  INDEX idx_hsp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 7. PRODUCT MANAGEMENT (PRD §10)
-- ============================================================================
CREATE TABLE IF NOT EXISTS product_categories (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_divisions (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_code  VARCHAR(30)  NOT NULL UNIQUE,
  brand_name    VARCHAR(100) NOT NULL,
  generic_name  VARCHAR(160),
  division_id   INT,
  category_id   INT,
  pack_size     VARCHAR(40),
  mrp           DECIMAL(10,2) NOT NULL DEFAULT 0,
  price         DECIMAL(10,2) NOT NULL DEFAULT 0,
  status        ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (division_id) REFERENCES product_divisions(id)   ON DELETE SET NULL,
  FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
  INDEX idx_prod_div (division_id),
  INDEX idx_prod_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_prices (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT NOT NULL,
  mrp           DECIMAL(10,2),
  price         DECIMAL(10,2),
  effective_from DATE NOT NULL,
  effective_to   DATE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 8. TOUR PLAN MANAGEMENT (PRD §11)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tour_plans (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  tp_code      VARCHAR(30)  NOT NULL UNIQUE,
  employee_id  BIGINT NOT NULL,
  plan_month   VARCHAR(20),
  plan_type    ENUM('Monthly','Weekly','Daily','Cycle') NOT NULL DEFAULT 'Weekly',
  status       ENUM('Draft','Submitted','Approved','Rejected') NOT NULL DEFAULT 'Draft',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  INDEX idx_tp_emp (employee_id),
  INDEX idx_tp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tour_plan_details (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  tour_plan_id  BIGINT NOT NULL,
  plan_date     DATE NOT NULL,
  beat_id       INT,
  doctors_count INT DEFAULT 0,
  chemists_count INT DEFAULT 0,
  notes         VARCHAR(255),
  FOREIGN KEY (tour_plan_id) REFERENCES tour_plans(id) ON DELETE CASCADE,
  FOREIGN KEY (beat_id)      REFERENCES beats(id)      ON DELETE SET NULL,
  INDEX idx_tpd_date (plan_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tour_plan_approvals (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  tour_plan_id  BIGINT NOT NULL,
  approver_id   BIGINT,
  approval_level ENUM('ASM','RSM','ZM','NSM'),
  status        ENUM('Submitted','Approved','Rejected') NOT NULL DEFAULT 'Submitted',
  comments      VARCHAR(255),
  approved_at   DATETIME,
  FOREIGN KEY (tour_plan_id) REFERENCES tour_plans(id)  ON DELETE CASCADE,
  FOREIGN KEY (approver_id)  REFERENCES employees(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 9. DAILY CALL REPORT (DCR) (PRD §12)
-- ============================================================================
CREATE TABLE IF NOT EXISTS dcr (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  dcr_code      VARCHAR(30)  NOT NULL UNIQUE,
  employee_id   BIGINT NOT NULL,
  visit_date    DATE NOT NULL,
  doctor_id     BIGINT,
  hospital_id   BIGINT,
  visit_type    ENUM('In-Clinic','Virtual','Phone') NOT NULL DEFAULT 'In-Clinic',
  duration_min  INT DEFAULT 0,
  gps_lat       DECIMAL(10,7),
  gps_lng       DECIMAL(10,7),
  feedback      TEXT,
  status        ENUM('Submitted','Approved','Pending') NOT NULL DEFAULT 'Pending',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  FOREIGN KEY (doctor_id)   REFERENCES doctors(id)   ON DELETE SET NULL,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE SET NULL,
  INDEX idx_dcr_date (visit_date),
  INDEX idx_dcr_emp (employee_id),
  INDEX idx_dcr_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dcr_products (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  dcr_id      BIGINT NOT NULL,
  product_id  BIGINT NOT NULL,
  quantity    INT DEFAULT 1,
  FOREIGN KEY (dcr_id)     REFERENCES dcr(id)      ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dcr_samples (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  dcr_id      BIGINT NOT NULL,
  product_id  BIGINT NOT NULL,
  quantity    INT DEFAULT 1,
  FOREIGN KEY (dcr_id)     REFERENCES dcr(id)      ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dcr_feedback (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  dcr_id      BIGINT NOT NULL,
  doctor_id   BIGINT,
  rating      TINYINT,
  notes       TEXT,
  follow_up_date DATE,
  FOREIGN KEY (dcr_id)    REFERENCES dcr(id)     ON DELETE CASCADE,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 10. ATTENDANCE MODULE (PRD §13)
-- ============================================================================
CREATE TABLE IF NOT EXISTS attendance (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id  BIGINT NOT NULL,
  att_date     DATE NOT NULL,
  check_in     DATETIME,
  check_out    DATETIME,
  location     VARCHAR(160),
  status       ENUM('Present','WFH','Leave','Absent') NOT NULL DEFAULT 'Absent',
  selfie_url   VARCHAR(255),
  geo_fenced   TINYINT(1) DEFAULT 0,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  UNIQUE KEY uq_att (employee_id, att_date),
  INDEX idx_att_date (att_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_locations (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  attendance_id BIGINT NOT NULL,
  gps_lat       DECIMAL(10,7),
  gps_lng       DECIMAL(10,7),
  captured_at   DATETIME,
  FOREIGN KEY (attendance_id) REFERENCES attendance(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_images (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  attendance_id BIGINT NOT NULL,
  image_type    ENUM('Check-In','Check-Out') DEFAULT 'Check-In',
  image_url     VARCHAR(255),
  FOREIGN KEY (attendance_id) REFERENCES attendance(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 11. GPS TRACKING MODULE (PRD §14)
-- ============================================================================
CREATE TABLE IF NOT EXISTS gps_tracking (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id  BIGINT NOT NULL,
  gps_lat      DECIMAL(10,7) NOT NULL,
  gps_lng      DECIMAL(10,7) NOT NULL,
  speed        DECIMAL(6,2),
  heading      INT,
  captured_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  INDEX idx_gps_emp_time (employee_id, captured_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS travel_logs (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id  BIGINT NOT NULL,
  travel_date  DATE NOT NULL,
  start_lat    DECIMAL(10,7),
  start_lng    DECIMAL(10,7),
  end_lat      DECIMAL(10,7),
  end_lng      DECIMAL(10,7),
  distance_km  DECIMAL(8,2),
  duration_min INT,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  INDEX idx_travel_emp (employee_id, travel_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visit_tracking (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id   BIGINT NOT NULL,
  doctor_id     BIGINT,
  chemist_id    BIGINT,
  hospital_id   BIGINT,
  visit_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gps_lat       DECIMAL(10,7),
  gps_lng       DECIMAL(10,7),
  geo_fenced    TINYINT(1) DEFAULT 0,
  duration_min  INT,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  FOREIGN KEY (doctor_id)   REFERENCES doctors(id)   ON DELETE SET NULL,
  FOREIGN KEY (chemist_id)  REFERENCES chemists(id)  ON DELETE SET NULL,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 12. SAMPLE MANAGEMENT (PRD §15)
-- ============================================================================
CREATE TABLE IF NOT EXISTS sample_inventory (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT NOT NULL,
  batch_no      VARCHAR(40),
  qty           INT NOT NULL DEFAULT 0,
  location      VARCHAR(120) DEFAULT 'Warehouse',
  status        ENUM('In-Stock','Allocated','Distributed','Returned') NOT NULL DEFAULT 'In-Stock',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  INDEX idx_sample_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sample_batches (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT NOT NULL,
  batch_no      VARCHAR(40) NOT NULL,
  qty           INT NOT NULL DEFAULT 0,
  manufacture_date DATE,
  expiry_date   DATE NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  UNIQUE KEY uq_batch (product_id, batch_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sample_transactions (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id    BIGINT NOT NULL,
  batch_no      VARCHAR(40),
  from_location VARCHAR(120),
  to_location   VARCHAR(120),
  qty           INT NOT NULL,
  txn_type      ENUM('Allocation','Transfer','Distribution','Return') NOT NULL,
  employee_id   BIGINT,
  txn_date      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id)  REFERENCES products(id)    ON DELETE CASCADE,
  FOREIGN KEY (employee_id) REFERENCES employees(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sample_allocations (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id   BIGINT NOT NULL,
  product_id    BIGINT NOT NULL,
  batch_no      VARCHAR(40),
  qty           INT NOT NULL,
  allocated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id)  REFERENCES products(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 13. ORDER MANAGEMENT (PRD §16)
-- ============================================================================
CREATE TABLE IF NOT EXISTS orders (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_no      VARCHAR(30)  NOT NULL UNIQUE,
  order_date    DATE NOT NULL,
  party_type    ENUM('Chemist','Hospital','Distributor') NOT NULL,
  chemist_id    BIGINT,
  hospital_id   BIGINT,
  employee_id   BIGINT,
  total_value   DECIMAL(12,2) NOT NULL DEFAULT 0,
  status        ENUM('Pending','Confirmed','Dispatched','Delivered','Cancelled') NOT NULL DEFAULT 'Pending',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (chemist_id)  REFERENCES chemists(id)   ON DELETE SET NULL,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE SET NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
  INDEX idx_order_date (order_date),
  INDEX idx_order_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id      BIGINT NOT NULL,
  product_id    BIGINT NOT NULL,
  quantity      INT NOT NULL,
  rate          DECIMAL(10,2) NOT NULL,
  discount      DECIMAL(5,2) DEFAULT 0,
  line_total    DECIMAL(12,2),
  FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_status (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id      BIGINT NOT NULL,
  status        VARCHAR(30) NOT NULL,
  changed_by    BIGINT,
  changed_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes         VARCHAR(255),
  FOREIGN KEY (order_id)   REFERENCES orders(id)     ON DELETE CASCADE,
  FOREIGN KEY (changed_by) REFERENCES employees(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 14. EXPENSE MANAGEMENT (PRD §17)
-- ============================================================================
CREATE TABLE IF NOT EXISTS expenses (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  exp_code      VARCHAR(30)  NOT NULL UNIQUE,
  employee_id   BIGINT NOT NULL,
  exp_date      DATE NOT NULL,
  category      ENUM('Travel','Fuel','Hotel','Food','Mobile','Internet','Misc') NOT NULL,
  amount        DECIMAL(12,2) NOT NULL,
  description   VARCHAR(255),
  receipt_url   VARCHAR(255),
  status        ENUM('Submitted','ASM-Approved','RSM-Approved','Finance-Approved','Rejected') NOT NULL DEFAULT 'Submitted',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  INDEX idx_exp_status (status),
  INDEX idx_exp_emp (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_receipts (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  expense_id   BIGINT NOT NULL,
  file_name    VARCHAR(255),
  file_url     VARCHAR(255),
  uploaded_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_approvals (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  expense_id    BIGINT NOT NULL,
  approver_id   BIGINT,
  approval_level ENUM('ASM','RSM','Finance'),
  status        ENUM('Submitted','Approved','Rejected') NOT NULL DEFAULT 'Submitted',
  comments      VARCHAR(255),
  approved_at   DATETIME,
  FOREIGN KEY (expense_id)  REFERENCES expenses(id)   ON DELETE CASCADE,
  FOREIGN KEY (approver_id) REFERENCES employees(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 15. LEAVE MANAGEMENT (PRD §18)
-- ============================================================================
CREATE TABLE IF NOT EXISTS leave_types (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(30) NOT NULL UNIQUE,
  quota INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leave_requests (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id   BIGINT NOT NULL,
  leave_type_id INT NOT NULL,
  from_date     DATE NOT NULL,
  to_date       DATE NOT NULL,
  days          INT NOT NULL DEFAULT 1,
  reason        VARCHAR(255),
  status        ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
  applied_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employee_id)   REFERENCES employees(id)   ON DELETE CASCADE,
  FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE RESTRICT,
  INDEX idx_leave_emp (employee_id),
  INDEX idx_leave_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leave_balances (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  employee_id   BIGINT NOT NULL,
  leave_type_id INT NOT NULL,
  year          INT NOT NULL,
  quota         INT NOT NULL DEFAULT 0,
  used          INT NOT NULL DEFAULT 0,
  balance       INT NOT NULL DEFAULT 0,
  FOREIGN KEY (employee_id)   REFERENCES employees(id)   ON DELETE CASCADE,
  FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE CASCADE,
  UNIQUE KEY uq_balance (employee_id, leave_type_id, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leave_approvals (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  leave_request_id BIGINT NOT NULL,
  approver_id     BIGINT,
  approval_level  ENUM('ASM','RSM','HR'),
  status          ENUM('Submitted','Approved','Rejected') NOT NULL DEFAULT 'Submitted',
  comments        VARCHAR(255),
  approved_at     DATETIME,
  FOREIGN KEY (leave_request_id) REFERENCES leave_requests(id) ON DELETE CASCADE,
  FOREIGN KEY (approver_id)      REFERENCES employees(id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 16. TASK MANAGEMENT (PRD §19)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tasks (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(200) NOT NULL,
  type          ENUM('Doctor Camp','CME Program','Promotion','Market Survey','Special Activity') NOT NULL,
  assigned_to   BIGINT,
  assigned_by   BIGINT,
  due_date      DATE,
  priority      ENUM('High','Medium','Low') NOT NULL DEFAULT 'Medium',
  status        ENUM('Open','In-Progress','Completed','Overdue') NOT NULL DEFAULT 'Open',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_to) REFERENCES employees(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_by) REFERENCES employees(id) ON DELETE SET NULL,
  INDEX idx_task_status (status),
  INDEX idx_task_due (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_assignments (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  task_id     BIGINT NOT NULL,
  employee_id BIGINT NOT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id)     REFERENCES tasks(id)      ON DELETE CASCADE,
  FOREIGN KEY (employee_id) REFERENCES employees(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_comments (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  task_id     BIGINT NOT NULL,
  employee_id BIGINT,
  comment     TEXT,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id)     REFERENCES tasks(id)      ON DELETE CASCADE,
  FOREIGN KEY (employee_id) REFERENCES employees(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 17. DOCUMENT MANAGEMENT (PRD §20)
-- ============================================================================
CREATE TABLE IF NOT EXISTS document_categories (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS documents (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(200) NOT NULL,
  category_id   INT,
  file_name     VARCHAR(255),
  file_url      VARCHAR(255),
  file_type     VARCHAR(20),
  file_size     BIGINT,
  uploaded_by   BIGINT,
  status        ENUM('Active','Archived') NOT NULL DEFAULT 'Active',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES document_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (uploaded_by) REFERENCES employees(id)           ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 18. NOTIFICATION ENGINE (PRD §24)
-- ============================================================================
CREATE TABLE IF NOT EXISTS notifications (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(200) NOT NULL,
  message       TEXT,
  channel       ENUM('Push','Email','SMS','WhatsApp') NOT NULL,
  recipient_id  BIGINT,
  recipient_role VARCHAR(40),
  is_read       TINYINT(1) NOT NULL DEFAULT 0,
  sent_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  read_at       DATETIME,
  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_read (is_read),
  INDEX idx_notif_recipient (recipient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_logs (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  notification_id BIGINT NOT NULL,
  provider      VARCHAR(40),
  provider_id   VARCHAR(120),
  status        ENUM('Queued','Sent','Delivered','Failed') NOT NULL DEFAULT 'Queued',
  error_message VARCHAR(255),
  sent_at       DATETIME,
  delivered_at  DATETIME,
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 19. AUDIT & SECURITY (PRD §26)
-- ============================================================================
CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT,
  action        VARCHAR(80)  NOT NULL,
  module        VARCHAR(60),
  ip_address    VARCHAR(45),
  user_agent    VARCHAR(255),
  status        ENUM('Success','Failed') NOT NULL DEFAULT 'Success',
  details       TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_time (created_at),
  INDEX idx_audit_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT,
  activity      VARCHAR(120),
  module        VARCHAR(60),
  ip_address    VARCHAR(45),
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_activity_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS security_logs (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT,
  event_type    VARCHAR(60),
  ip_address    VARCHAR(45),
  severity      ENUM('Info','Warning','Critical') NOT NULL DEFAULT 'Info',
  details       TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_security_severity (severity),
  INDEX idx_security_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 20. SYSTEM SETTINGS (PRD §27)
-- ============================================================================
CREATE TABLE IF NOT EXISTS settings (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  setting_key   VARCHAR(80) NOT NULL UNIQUE,
  setting_value TEXT,
  description   VARCHAR(255),
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- 21. SEED DATA
-- ============================================================================

-- Roles
INSERT INTO roles (name, description, is_system) VALUES
  ('SUPER_ADMIN', 'Full system access', 1),
  ('NSM',         'National Sales Manager', 1),
  ('ZM',          'Zonal Manager', 1),
  ('RSM',         'Regional Sales Manager', 1),
  ('ASM',         'Area Sales Manager', 1),
  ('MR',          'Medical Representative', 1),
  ('HR',          'HR Team', 1),
  ('FINANCE',     'Finance Team', 1);

-- Permissions
INSERT INTO permissions (code, module, description) VALUES
  ('dashboard.view',    'Dashboard',  'View dashboard'),
  ('doctors.manage',    'Doctors',    'Add/Edit/Delete doctors'),
  ('chemists.manage',   'Chemists',   'Add/Edit/Delete chemists'),
  ('hospitals.manage',  'Hospitals',  'Add/Edit/Delete hospitals'),
  ('products.manage',   'Products',   'Add/Edit/Delete products'),
  ('territory.manage',  'Territory',  'Manage zones/regions/areas'),
  ('tourplan.manage',   'Tour Plan',  'Create/approve tour plans'),
  ('dcr.manage',        'DCR',        'Submit/approve DCR'),
  ('attendance.manage', 'Attendance', 'Mark attendance'),
  ('expenses.manage',   'Expenses',   'Submit/approve expenses'),
  ('orders.manage',     'Orders',     'Create orders'),
  ('leave.manage',      'Leave',      'Apply/approve leave'),
  ('tasks.manage',      'Tasks',      'Assign tasks'),
  ('reports.view',      'Reports',    'View reports'),
  ('audit.view',        'Audit',      'View audit logs'),
  ('settings.manage',   'Settings',   'Manage system settings');

-- Doctor categories
INSERT INTO doctor_categories (code, name, weight) VALUES
  ('A', 'Category A - High Potential', 90),
  ('B', 'Category B - Medium Potential', 60),
  ('C', 'Category C - Low Potential', 30);

-- Doctor specialties
INSERT INTO doctor_specialties (name) VALUES
  ('Cardiologist'), ('Physician'), ('Pediatrician'), ('Orthopedic'),
  ('Neurologist'), ('Gynecologist'), ('Psychiatrist'), ('Endocrinologist'),
  ('Dermatologist'), ('ENT');

-- Doctor frequency
INSERT INTO doctor_frequency (name) VALUES
  ('Weekly'), ('Bi-Weekly'), ('Monthly');

-- Hospital types
INSERT INTO hospital_types (name) VALUES
  ('Multi-Specialty'), ('Government'), ('Private'), ('Trust');

-- Leave types
INSERT INTO leave_types (name, quota) VALUES
  ('Casual', 12), ('Sick', 7), ('Earned', 15), ('Special', 5);

-- Product categories
INSERT INTO product_categories (name) VALUES
  ('Tablet'), ('Capsule'), ('Syrup'), ('Injection'), ('Cream');

-- Product divisions
INSERT INTO product_divisions (name) VALUES
  ('Cardiac'), ('Diabetic'), ('Anti-Infective'), ('Gastro'),
  ('Neuro'), ('Vitamin'), ('Pain');

-- Zones
INSERT INTO zones (code, name, manager) VALUES
  ('NZ', 'North Zone',   'Vikram Sharma'),
  ('SZ', 'South Zone',   'Karthik Reddy'),
  ('EZ', 'East Zone',    'Subir Das'),
  ('WZ', 'West Zone',    'Nilesh Patil'),
  ('CZ', 'Central Zone', 'Rakesh Tiwari');

-- Regions
INSERT INTO regions (code, name, zone_id, manager) VALUES
  ('R01', 'Delhi NCR',  1, 'Sunita Verma'),
  ('R02', 'Punjab',     1, 'Harpreet Singh'),
  ('R03', 'Chennai',    2, 'Dinesh Babu'),
  ('R04', 'Bangalore',  2, 'Anand Kumar'),
  ('R05', 'Kolkata',    3, 'Rohit Sen'),
  ('R06', 'Mumbai',     4, 'Sachin Kale');

-- Areas
INSERT INTO areas (code, name, region_id, manager) VALUES
  ('A01', 'Central Delhi', 1, 'Manoj Gupta'),
  ('A02', 'South Delhi',   1, 'Ravi Ahuja'),
  ('A03', 'T Nagar',       3, 'Priya Iyer'),
  ('A04', 'Indiranagar',   4, 'Rohit Nair'),
  ('A05', 'Bandra',        6, 'Neha Joshi');

-- Territories
INSERT INTO territories (code, name, area_id, hq) VALUES
  ('T01', 'CP-Karol Bagh', 1, 'New Delhi'),
  ('T02', 'Saket-Malviya', 2, 'New Delhi'),
  ('T03', 'TN-Beat-12',    3, 'Chennai'),
  ('T04', 'BLR-03',        4, 'Bangalore'),
  ('T05', 'MUM-Beat-04',   5, 'Mumbai');

-- Beats
INSERT INTO beats (code, name, territory_id, visit_day) VALUES
  ('B01', 'CP Loop',     1, 'Mon'),
  ('B02', 'Karol Bagh',  1, 'Tue'),
  ('B03', 'Saket Loop',  2, 'Wed'),
  ('B04', 'T Nagar',     3, 'Mon'),
  ('B05', 'Indiranagar', 4, 'Thu'),
  ('B06', 'Bandra',      5, 'Fri');

-- Employees (managers first, then MRs)
INSERT INTO employees (emp_code, name, designation, department, email, mobile, doj, manager_id, territory_id, hq, status) VALUES
  ('EMP-2020-002', 'Vikram Sharma',  'Zonal Manager',              'Sales', 'vikram.sharma@pharmasfa.in', '+91 98100 10003', '2020-05-10', NULL, NULL, 'New Delhi', 'Active'),
  ('EMP-2021-008', 'Sunita Verma',   'Regional Sales Manager',     'Sales', 'sunita.verma@pharmasfa.in',  '+91 98100 10004', '2021-03-20', 1,    NULL, 'New Delhi', 'Active'),
  ('EMP-2022-014', 'Manoj Gupta',    'Area Sales Manager',         'Sales', 'manoj.gupta@pharmasfa.in',   '+91 98100 10005', '2022-01-15', 2,    1,    'New Delhi', 'Active'),
  ('EMP-2023-001', 'Arjun Singh',    'Medical Representative',     'Sales', 'arjun.singh@pharmasfa.in',   '+91 98100 10006', '2023-04-12', 3,    1,    'New Delhi', 'Active'),
  ('EMP-2023-031', 'Faisal Ahmed',   'Medical Representative',     'Sales', 'faisal.ahmed@pharmasfa.in',  '+91 98100 10031', '2023-09-01', 2,    NULL, 'Kolkata',   'Active'),
  ('EMP-2024-021', 'Rohit Nair',     'Medical Representative',     'Sales', 'rohit.nair@pharmasfa.in',    '+91 98100 10021', '2024-02-05', NULL, 4,    'Bangalore', 'Active'),
  ('EMP-2024-022', 'Neha Joshi',     'Medical Representative',     'Sales', 'neha.joshi@pharmasfa.in',    '+91 98100 10022', '2024-03-18', NULL, 5,    'Mumbai',    'Active'),
  ('EMP-2023-002', 'Priya Iyer',     'Medical Representative',     'Sales', 'priya.iyer@pharmasfa.in',    '+91 98100 10007', '2023-06-01', NULL, 3,    'Chennai',   'Active');

-- Users (password = 'admin@123' hashed — replace hash with your app's bcrypt output)
-- The hash below is bcrypt for "admin@123" — replace with your own generated hash.
INSERT INTO users (username, email, mobile, password_hash, mfa_enabled, status, employee_id) VALUES
  ('admin',      'admin@pharmasfa.in',          '+91 98100 10001', '$2b$12$uQ8v3wQ2kH7yF1pZ5rX9jOcVkLmN0bB8sG4dE6tY1uI3oP5qW7rS', 1, 'Active', NULL),
  ('nsm.mehta',  'anil.mehta@pharmasfa.in',     '+91 98100 10002', '$2b$12$uQ8v3wQ2kH7yF1pZ5rX9jOcVkLmN0bB8sG4dE6tY1uI3oP5qW7rS', 1, 'Active', NULL),
  ('asm.gupta',  'manoj.gupta@pharmasfa.in',    '+91 98100 10005', '$2b$12$uQ8v3wQ2kH7yF1pZ5rX9jOcVkLmN0bB8sG4dE6tY1uI3oP5qW7rS', 1, 'Active', 3),
  ('mr.singh',   'arjun.singh@pharmasfa.in',    '+91 98100 10006', '$2b$12$uQ8v3wQ2kH7yF1pZ5rX9jOcVkLmN0bB8sG4dE6tY1uI3oP5qW7rS', 1, 'Active', 4),
  ('mr.iyer',    'priya.iyer@pharmasfa.in',     '+91 98100 10007', '$2b$12$uQ8v3wQ2kH7yF1pZ5rX9jOcVkLmN0bB8sG4dE6tY1uI3oP5qW7rS', 1, 'Active', 8);

-- User roles
INSERT INTO user_roles (user_id, role_id) VALUES
  (1, 1),  -- admin → SUPER_ADMIN
  (2, 2),  -- nsm.mehta → NSM
  (3, 5),  -- asm.gupta → ASM
  (4, 6),  -- mr.singh → MR
  (5, 6);  -- mr.iyer  → MR

-- Hospitals
INSERT INTO hospitals (hospital_code, name, type_id, category, address, territory_id, capacity, status) VALUES
  ('HSP-0001', 'Apollo Hospital',      1, 'A', 'Sarita Vihar, Delhi',    1, 710,  'Active'),
  ('HSP-0002', 'Fortis Hospital',      1, 'A', 'Vasant Kunj, Delhi',     1, 680,  'Active'),
  ('HSP-0003', 'AIIMS',                2, 'A', 'Ansari Nagar, Delhi',    1, 2400, 'Active'),
  ('HSP-0004', 'Max Healthcare',       1, 'A', 'Saket, Delhi',           1, 530,  'Active'),
  ('HSP-0005', 'Kokilaben Hospital',   1, 'A', 'Andheri, Mumbai',        5, 750,  'Active'),
  ('HSP-0006', 'NIMHANS',              2, 'A', 'Bangalore',              4, 1200, 'Active'),
  ('HSP-0007', 'Rainbow Hospital',     3, 'A', 'Jubilee Hills',          3, 180,  'Active'),
  ('HSP-0008', 'Lilavati Hospital',    1, 'A', 'Bandra, Mumbai',         5, 320,  'Active'),
  ('HSP-0009', 'Ruby Hall',            3, 'B', 'Pune',                   5, 250,  'Active'),
  ('HSP-0010', 'PGIMER',               2, 'A', 'Kolkata',               NULL, 1500,'Active');

-- Doctors
INSERT INTO doctors (doctor_code, name, qualification, specialty_id, category_id, hospital_id, area, mobile, potential_score, frequency_id, rx_potential, status) VALUES
  ('DOC-0001', 'Dr. Anand Patel',       'MD, DM', 1, 1, 1, 'Central Delhi', '+91 99100 20001', 92, 1, 'High',   'Active'),
  ('DOC-0002', 'Dr. Meera Krishnan',    'MD',     2, 1, 2, 'T Nagar',       '+91 99100 20002', 88, 1, 'High',   'Active'),
  ('DOC-0003', 'Dr. Rajiv Menon',       'MS',     4, 2, 3, 'South Delhi',   '+91 99100 20003', 74, 2, 'Medium', 'Active'),
  ('DOC-0004', 'Dr. Sushma Reddy',      'MD, DCH',3, 1, 7, 'Jubilee Hills', '+91 99100 20004', 81, 1, 'High',   'Active'),
  ('DOC-0005', 'Dr. Imran Sheikh',      'MD',     5, 2, 6, 'Bangalore',     '+91 99100 20005', 68, 2, 'Medium', 'Active'),
  ('DOC-0006', 'Dr. Kavita Nair',       'MD, DGO',6, 2, 8, 'Bandra',        '+91 99100 20006', 71, 2, 'Medium', 'Active'),
  ('DOC-0007', 'Dr. Sanjay Bhattacharya','MD',    7, 3,10, 'Kolkata',       '+91 99100 20007', 55, 3, 'Low',    'Active'),
  ('DOC-0008', 'Dr. Anjali Deshpande',  'MD, DM', 8, 1, 5, 'Andheri',       '+91 99100 20008', 85, 1, 'High',   'Active'),
  ('DOC-0009', 'Dr. Vivek Joshi',       'MS',     10,3, 9, 'Pune',          '+91 99100 20009', 48, 3, 'Low',    'Active'),
  ('DOC-0010', 'Dr. Pooja Agarwal',     'MD, DNB',9, 2, 4, 'Saket',         '+91 99100 20010', 77, 2, 'Medium', 'Active');

-- Chemists
INSERT INTO chemists (chemist_code, name, type, contact_person, mobile, address, territory_id, status) VALUES
  ('CHM-0001', 'Apollo Pharmacy',                    'Retail',   'Ramesh',  '+91 80100 30001', '12 CP, New Delhi',     1, 'Active'),
  ('CHM-0002', 'MedPlus Store',                      'Retail',   'Suresh',  '+91 80100 30002', '45 T Nagar, Chennai',   3, 'Active'),
  ('CHM-0003', 'Wellness Forever',                   'Retail',   'Deepak',  '+91 80100 30003', 'Bandra West, Mumbai',   5, 'Active'),
  ('CHM-0004', 'All India Medical Distributors',     'Wholesale','Prakash', '+91 80100 30004', 'Lalbaug, Mumbai',       5, 'Active'),
  ('CHM-0005', 'Fortis Hospital Pharmacy',           'Hospital', 'Anita',   '+91 80100 30005', 'Vasant Kunj, Delhi',    1, 'Active'),
  ('CHM-0006', 'Guardian Pharmacy',                  'Retail',   'Vijay',   '+91 80100 30006', 'Sector 18, Noida',     NULL,'Active');

-- Products
INSERT INTO products (product_code, brand_name, generic_name, division_id, category_id, pack_size, mrp, price, status) VALUES
  ('PRD-001', 'Cardiolex-50',  'Atenolol 50mg',         1, 1, '10x10', 85,  71,  'Active'),
  ('PRD-002', 'Glycimet-500',  'Metformin 500mg',       2, 1, '15x10', 145, 122, 'Active'),
  ('PRD-003', 'Amoxilin-CV',   'Amoxicillin 625mg',     3, 1, '10x10', 220, 187, 'Active'),
  ('PRD-004', 'Pantosun-40',   'Pantoprazole 40mg',     4, 1, '15x10', 165, 140, 'Active'),
  ('PRD-005', 'Calmnit-NS',    'Clonazepam 0.5mg',      5, 1, '10x10', 95,  80,  'Active'),
  ('PRD-006', 'Folvite-Plus',  'Folic Acid 5mg',        6, 1, '10x10', 55,  47,  'Active'),
  ('PRD-007', 'DoloCare-650',  'Paracetamol 650mg',     7, 1, '15x10', 30,  25,  'Active'),
  ('PRD-008', 'VitaD3-60K',    'Cholecalciferol 60K',   6, 2, '4x1x4', 175, 149, 'Active');

-- Tour plans
INSERT INTO tour_plans (tp_code, employee_id, plan_month, plan_type, status) VALUES
  ('TP001', 4, 'June 2025', 'Weekly', 'Approved'),
  ('TP002', 4, 'June 2025', 'Weekly', 'Approved'),
  ('TP003', 8, 'June 2025', 'Weekly', 'Submitted'),
  ('TP004', 6, 'June 2025', 'Weekly', 'Draft'),
  ('TP005', 7, 'June 2025', 'Weekly', 'Approved'),
  ('TP006', 5, 'June 2025', 'Weekly', 'Rejected');

-- Tour plan details
INSERT INTO tour_plan_details (tour_plan_id, plan_date, beat_id, doctors_count, chemists_count) VALUES
  (1, '2025-06-23', 1, 6, 3),
  (2, '2025-06-24', 3, 5, 2),
  (3, '2025-06-23', 4, 7, 4),
  (4, '2025-06-23', 5, 4, 2),
  (5, '2025-06-23', 6, 6, 3),
  (6, '2025-06-23', 2, 5, 2);

-- DCR
INSERT INTO dcr (dcr_code, employee_id, visit_date, doctor_id, hospital_id, visit_type, duration_min, gps_lat, gps_lng, feedback, status) VALUES
  ('DC001', 4, '2025-06-23', 1, 1, 'In-Clinic', 18, 28.5355000, 77.3910000, 'Doctor showed interest in Cardiolex. Requested clinical study material.', 'Approved'),
  ('DC002', 4, '2025-06-23', 8, 5, 'In-Clinic', 15, 28.5355000, 77.3910000, 'Positive response. Will start prescribing next week.', 'Submitted'),
  ('DC003', 8, '2025-06-23', 2, 2, 'In-Clinic', 22, 13.0827000, 80.2707000, 'Already prescribing Glycimet. Happy with results.', 'Approved'),
  ('DC004', 6, '2025-06-22', 4, 7, 'In-Clinic', 16, 12.9716000, 77.5946000, 'Asked for pediatric dosage chart.', 'Pending'),
  ('DC005', 7, '2025-06-22', 6, 8, 'In-Clinic', 14, 19.0760000, 72.8777000, 'Good meeting. Discussed new combo therapy.', 'Approved'),
  ('DC006', 4, '2025-06-22', 10, 4, 'Virtual',  10, 28.5355000, 77.3910000, 'Doctor busy. Brief virtual update done.', 'Pending');

-- Attendance
INSERT INTO attendance (employee_id, att_date, check_in, check_out, location, status, selfie_url, geo_fenced) VALUES
  (4, '2025-06-23', '2025-06-23 09:12:00', '2025-06-23 18:45:00', 'CP, New Delhi',          'Present', '/uploads/selfie/u4-1.jpg', 1),
  (8, '2025-06-23', '2025-06-23 08:55:00', '2025-06-23 18:30:00', 'T Nagar, Chennai',       'Present', '/uploads/selfie/u8-1.jpg', 1),
  (6, '2025-06-23', '2025-06-23 09:30:00', '2025-06-23 18:00:00', 'Indiranagar, Bangalore', 'Present', '/uploads/selfie/u6-1.jpg', 1),
  (7, '2025-06-23', '2025-06-23 09:05:00', '2025-06-23 18:15:00', 'Bandra, Mumbai',         'Present', '/uploads/selfie/u7-1.jpg', 1),
  (5, '2025-06-23', NULL, NULL, NULL, 'Leave', NULL, 0),
  (3, '2025-06-23', '2025-06-23 09:30:00', '2025-06-23 17:30:00', 'HQ, New Delhi',          'Present', '/uploads/selfie/u3-1.jpg', 1);

-- Expenses
INSERT INTO expenses (exp_code, employee_id, exp_date, category, amount, description, receipt_url, status) VALUES
  ('EX001', 4, '2025-06-23', 'Fuel',  850,  'Petrol for field visits - CP, Saket',    '/uploads/r/ex001.jpg', 'Finance-Approved'),
  ('EX002', 4, '2025-06-22', 'Food',  320,  'Lunch during field work',                '/uploads/r/ex002.jpg', 'RSM-Approved'),
  ('EX003', 8, '2025-06-23', 'Travel',1200, 'Cab - 4 doctor visits',                  '/uploads/r/ex003.jpg', 'ASM-Approved'),
  ('EX004', 6, '2025-06-22', 'Hotel', 2800, 'Stay at Bangalore (CME program)',        '/uploads/r/ex004.jpg', 'Submitted'),
  ('EX005', 7, '2025-06-21', 'Mobile',500,  'Mobile bill reimbursement',              NULL,                   'Finance-Approved'),
  ('EX006', 5, '2025-06-20', 'Misc',  450,  'Stationery for camp',                    '/uploads/r/ex006.jpg', 'Rejected');

-- Orders
INSERT INTO orders (order_no, order_date, party_type, chemist_id, hospital_id, employee_id, total_value, status) VALUES
  ('ORD-25-0012', '2025-06-23', 'Chemist',     1, NULL, 4, 18450, 'Dispatched'),
  ('ORD-25-0013', '2025-06-23', 'Hospital',    NULL, 2, 4, 32600, 'Confirmed'),
  ('ORD-25-0014', '2025-06-23', 'Chemist',     2, NULL, 8, 9800,  'Pending'),
  ('ORD-25-0015', '2025-06-22', 'Distributor', 4, NULL, 7, 78500, 'Delivered'),
  ('ORD-25-0016', '2025-06-22', 'Chemist',     3, NULL, 7, 22100, 'Delivered'),
  ('ORD-25-0017', '2025-06-21', 'Chemist',     6, NULL, 4, 14200, 'Cancelled');

-- Sample inventory
INSERT INTO sample_inventory (product_id, batch_no, qty, location, status) VALUES
  (1, 'CRD2401', 100, 'Arjun Singh',  'Distributed'),
  (2, 'GLY2403', 80,  'Arjun Singh',  'Allocated'),
  (4, 'PAN2402', 60,  'Priya Iyer',   'Distributed'),
  (3, 'AMX2405', 500, 'Warehouse',    'In-Stock'),
  (8, 'VTD2404', 40,  'Rohit Nair',   'Returned'),
  (7, 'DLC2406', 800, 'Warehouse',    'In-Stock');

-- Leave requests
INSERT INTO leave_requests (employee_id, leave_type_id, from_date, to_date, days, reason, status) VALUES
  (5, 2, '2025-06-23', '2025-06-24', 2, 'Fever and cold',         'Approved'),
  (4, 1, '2025-07-04', '2025-07-05', 2, 'Personal work',          'Pending'),
  (8, 3, '2025-08-10', '2025-08-14', 5, 'Vacation with family',   'Approved'),
  (6, 4, '2025-07-12', '2025-07-12', 1, 'Marriage function',      'Approved'),
  (7, 1, '2025-07-20', '2025-07-21', 2, 'Family function',        'Pending');

-- Leave balances (year 2025)
INSERT INTO leave_balances (employee_id, leave_type_id, year, quota, used, balance) VALUES
  (4, 1, 2025, 12, 2, 10),
  (4, 2, 2025, 7,  1, 6),
  (4, 3, 2025, 15, 0, 15),
  (5, 2, 2025, 7,  2, 5),
  (6, 3, 2025, 15, 1, 14),
  (7, 1, 2025, 12, 3, 9),
  (8, 1, 2025, 12, 1, 11),
  (8, 3, 2025, 15, 5, 10);

-- Tasks
INSERT INTO tasks (title, type, assigned_to, assigned_by, due_date, priority, status) VALUES
  ('Cardiac Camp at Apollo Hospital',      'Doctor Camp',     4, 3, '2025-06-28', 'High',   'In-Progress'),
  ('CME on Diabetes Management',           'CME Program',     8, NULL, '2025-07-05', 'High',   'Open'),
  ('Launch Promo for Cardiolex-50',        'Promotion',       6, NULL, '2025-07-10', 'Medium', 'Open'),
  ('Competitor Pricing Survey - South',    'Market Survey',   8, NULL, '2025-06-30', 'Medium', 'In-Progress'),
  ('Doctor felicitation event - Mumbai',   'Special Activity',7, NULL, '2025-06-25', 'High',   'Overdue'),
  ('Patient awareness drive - Kolkata',    'Doctor Camp',     5, NULL, '2025-06-20', 'Low',    'Completed');

-- Notifications
INSERT INTO notifications (title, message, channel, recipient_id, is_read, sent_at) VALUES
  ('Tour Plan approved by Manoj Gupta', 'Your weekly TP for CP-Karol Bagh has been approved.', 'Push',    4, 0, '2025-06-23 10:15:00'),
  ('Expense EX001 approved by Finance',  'Expense ₹850 (Fuel) approved and processed.',       'Email',   4, 0, '2025-06-23 09:30:00'),
  ('New CME task assigned',              'CME on Diabetes Management assigned to you.',        'Push',    8, 0, '2025-06-23 08:45:00'),
  ('Leave request approved',             'Your sick leave (23-24 Jun) has been approved.',     'SMS',     5, 1, '2025-06-23 07:20:00'),
  ('Weekly DCR pending reminder',        'Submit your DCR for this week by EOD Saturday.',     'WhatsApp',NULL, 1, '2025-06-23 06:00:00');

-- Audit logs
INSERT INTO audit_logs (user_id, action, module, ip_address, status, created_at) VALUES
  (1, 'Login',                  'Authentication', '10.0.12.45',  'Success', '2025-06-23 09:00:12'),
  (4, 'DCR Create',             'DCR',            '10.0.12.88',  'Success', '2025-06-23 11:23:45'),
  (3, 'Tour Plan Approve',      'Tour Plan',      '10.0.12.50',  'Success', '2025-06-23 12:15:30'),
  (NULL, 'Login Attempt',       'Authentication', '203.45.10.8', 'Failed',  '2025-06-23 13:01:09'),
  (5, 'Expense Approve',        'Expense',        '10.0.12.51',  'Success', '2025-06-23 14:45:22'),
  (8, 'Attendance Check-In',    'Attendance',     '10.0.13.20',  'Success', '2025-06-23 08:55:01');

-- System settings
INSERT INTO settings (setting_key, setting_value, description) VALUES
  ('app_name',             'PharmaSFA Pro',           'Application name'),
  ('app_version',          '1.0',                     'Application version'),
  ('session_timeout',      '30',                      'Session timeout in minutes'),
  ('mfa_enabled',          'true',                    'Enable MFA globally'),
  ('device_binding',       'true',                    'Bind user to single device'),
  ('password_min_length',  '8',                       'Minimum password length'),
  ('login_max_attempts',   '5',                       'Max failed login attempts before lockout'),
  ('lockout_duration_min', '15',                      'Lockout duration in minutes'),
  ('offline_sync_mode',    'background_delta',        'Offline sync strategy'),
  ('default_currency',     'INR',                     'Default currency code'),
  ('timezone',             'Asia/Kolkata',            'Default application timezone');

-- ============================================================================
-- 22. VIEWS FOR REPORTING (optional, helpful for BI)
-- ============================================================================
-- View: Employee hierarchy with zone/region/area
CREATE OR REPLACE VIEW v_employee_hierarchy AS
  SELECT
    e.id, e.emp_code, e.name, e.designation,
    e.department, e.hq, e.status,
    m.name  AS manager_name,
    t.name  AS territory, a.name AS area, r.name AS region, z.name AS zone
  FROM employees e
  LEFT JOIN employees  e2 ON e2.id = e.manager_id
  LEFT JOIN employees  m  ON m.id  = e.manager_id
  LEFT JOIN territories t ON t.id  = e.territory_id
  LEFT JOIN areas       a ON a.id  = t.area_id
  LEFT JOIN regions     r ON r.id  = a.region_id
  LEFT JOIN zones       z ON z.id  = r.zone_id;

-- View: DCR summary per MR per day
CREATE OR REPLACE VIEW v_dcr_summary AS
  SELECT
    d.employee_id, e.name AS mr_name, d.visit_date,
    COUNT(d.id) AS visits,
    SUM(d.duration_min) AS total_minutes,
    SUM(CASE WHEN d.status='Approved' THEN 1 ELSE 0 END) AS approved_count
  FROM dcr d
  JOIN employees e ON e.id = d.employee_id
  GROUP BY d.employee_id, e.name, d.visit_date;

-- View: Expense summary per employee
CREATE OR REPLACE VIEW v_expense_summary AS
  SELECT
    employee_id,
    COUNT(*) AS total_claims,
    SUM(amount) AS total_amount,
    SUM(CASE WHEN status='Finance-Approved' THEN amount ELSE 0 END) AS approved_amount,
    SUM(CASE WHEN status='Rejected' THEN 1 ELSE 0 END) AS rejected_count
  FROM expenses
  GROUP BY employee_id;

-- View: Order summary by status
CREATE OR REPLACE VIEW v_order_summary AS
  SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_value) AS total_value
  FROM orders
  GROUP BY status;

-- ============================================================================
-- 23. DONE — restore FK checks
-- ============================================================================
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- SUMMARY
--   Total tables:    50
--   Total views:     4
--   Seed rows:       ~150 across all master + transactional tables
--   Default login:   admin / admin@123  (REPLACE the bcrypt hash in production!)
--
-- PRD module coverage:
--   §4  Auth         : roles, permissions, users, user_roles, login_history, otp_logs, devices
--   §5  Territory    : zones, regions, areas, territories, hq_master, beats
--   §6  Employee     : employees, employee_documents
--   §7  Doctor       : doctors, doctor_categories, doctor_specialties, doctor_frequency
--   §8  Chemist      : chemists, chemist_contacts
--   §9  Hospital     : hospitals, hospital_types
--   §10 Product      : products, product_categories, product_divisions, product_prices
--   §11 Tour Plan    : tour_plans, tour_plan_details, tour_plan_approvals
--   §12 DCR          : dcr, dcr_products, dcr_samples, dcr_feedback
--   §13 Attendance   : attendance, attendance_locations, attendance_images
--   §14 GPS          : gps_tracking, travel_logs, visit_tracking
--   §15 Samples      : sample_inventory, sample_batches, sample_transactions, sample_allocations
--   §16 Orders       : orders, order_items, order_status
--   §17 Expenses     : expenses, expense_receipts, expense_approvals
--   §18 Leave        : leave_types, leave_requests, leave_balances, leave_approvals
--   §19 Tasks        : tasks, task_assignments, task_comments
--   §20 Documents    : documents, document_categories
--   §24 Notifications: notifications, notification_logs
--   §26 Audit        : audit_logs, activity_logs, security_logs
--   §27 Settings     : settings
-- ============================================================================