-- ساخت اولیه ساختار دیتابیس
-- این فایل باید ابتدا اجرا شود.

-- ۱. جدول استان‌ها
CREATE TABLE provinces_list (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  ordered TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۲. جدول شهرها
CREATE TABLE provinces_cities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prov_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  ordered TINYINT NOT NULL DEFAULT 0,
  CONSTRAINT fk_city_province FOREIGN KEY (prov_id) REFERENCES provinces_list(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۳. جدول دسته‌بندی مشتریان
CREATE TABLE `customers_cat` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `descri` TEXT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1: Active, 0: Inactive',
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1: Deleted, 0: Normal',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۴. جدول کاربران سیستم (شامل رانندگان و بازاریاب‌ها)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    mobile VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    UNIQUE KEY uq_user_mobile (mobile),
    status ENUM('pending','active','inactive','blocked') NOT NULL DEFAULT 'pending',
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۵. جدول مسیرها (Zones) - (ستون driver_id به جدول واسط منتقل شد)
CREATE TABLE `zones` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL COMMENT 'نام مسیر / منطقه',
    `area_name` VARCHAR(150) NULL COMMENT 'ناحیه / محدوده زون',
    `description` VARCHAR(255) NULL COMMENT 'توضیحات مسیر',
    `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1: Active, 0: Inactive',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۵.۱. جدول ماشین‌های پخش
CREATE TABLE `vehicles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `vehicle_code` VARCHAR(50) NOT NULL COMMENT 'کد داخلی ماشین',
    `vehicle_type` VARCHAR(80) NOT NULL COMMENT 'نیسان، ایسوزو و...',
    `plate_number` VARCHAR(30) NOT NULL COMMENT 'شماره پلاک',
    `model_year` SMALLINT UNSIGNED NULL COMMENT 'سال ساخت',
    `color` VARCHAR(50) NULL,
    `ownership_type` ENUM('company', 'personal') NOT NULL DEFAULT 'company' COMMENT 'مالکیت شرکت یا شخص',
    `owner_name` VARCHAR(150) NULL COMMENT 'نام مالک در صورت شخصی بودن',
    `owner_mobile` VARCHAR(20) NULL,
    `status` ENUM('active', 'inactive', 'maintenance') NOT NULL DEFAULT 'active',
    `notes` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_vehicle_code` (`vehicle_code`),
    UNIQUE KEY `uq_vehicle_plate` (`plate_number`),
    INDEX `idx_vehicles_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۵.۲. تاریخچه تخصیص راننده به ماشین
CREATE TABLE `vehicle_driver_assignments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `vehicle_id` BIGINT UNSIGNED NOT NULL,
    `driver_id` BIGINT UNSIGNED NOT NULL,
    `assigned_from` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `assigned_to` DATETIME NULL,
    `status` ENUM('active', 'ended') NOT NULL DEFAULT 'active',
    `notes` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_vehicle_driver_assignments_vehicle`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_vehicle_driver_assignments_driver`
        FOREIGN KEY (`driver_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_vehicle_driver_assignments_vehicle` (`vehicle_id`, `assigned_from`),
    INDEX `idx_vehicle_driver_assignments_driver` (`driver_id`, `assigned_from`),
    INDEX `idx_vehicle_driver_assignments_active` (`vehicle_id`, `status`, `assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۵.۳. تاریخچه تخصیص ماشین به زون
CREATE TABLE `zone_vehicle_assignments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `zone_id` INT NOT NULL,
    `vehicle_id` BIGINT UNSIGNED NOT NULL,
    `assigned_from` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `assigned_to` DATETIME NULL,
    `status` ENUM('active', 'ended') NOT NULL DEFAULT 'active',
    `notes` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_zone_vehicle_assignments_zone`
        FOREIGN KEY (`zone_id`) REFERENCES `zones`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_zone_vehicle_assignments_vehicle`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE RESTRICT,
    INDEX `idx_zone_vehicle_assignments_zone` (`zone_id`, `assigned_from`),
    INDEX `idx_zone_vehicle_assignments_vehicle` (`vehicle_id`, `assigned_from`),
    INDEX `idx_zone_vehicle_assignments_active` (`vehicle_id`, `status`, `assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۵.۴. جدول قدیمی تخصیص مستقیم رانندگان به مسیرها؛ برای سازگاری داده‌های قبلی حفظ شده است.
CREATE TABLE `zone_drivers` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `zone_id` INT NOT NULL COMMENT 'مسیر / زون',
    `driver_id` BIGINT UNSIGNED NOT NULL COMMENT 'راننده',
    `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1: راننده اصلی مسیر، 0: راننده کمکی',
    `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1: فعال، 0: غیرفعال',
    `assigned_from` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'تاریخ شروع تخصیص',
    `assigned_to` DATETIME NULL COMMENT 'تاریخ پایان تخصیص',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_zone_driver` (`zone_id`, `driver_id`),
    INDEX `idx_zone_drivers_driver` (`driver_id`),
    INDEX `idx_zone_drivers_zone_status` (`zone_id`, `status`),
    CONSTRAINT `fk_zone_drivers_zone` FOREIGN KEY (`zone_id`) REFERENCES `zones`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_zone_drivers_driver` FOREIGN KEY (`driver_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۶. جدول نقش‌ها
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۷. جدول دسترسی‌ها
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۸. جدول رابط کاربر و نقش
CREATE TABLE user_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_role (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_persian_ci;

-- ۹. جدول رابط نقش و دسترسی
CREATE TABLE role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_permission (role_id, permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۱۰. جدول لاگ‌ها
CREATE TABLE logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    log_type VARCHAR(100) NOT NULL, 
    log_event VARCHAR(250) NOT NULL, 
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_al_event (log_type),
    CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۱۱. جدول مشتریان (با اتصال کلید خارجی به جدول مسیرها یا همان zones)
CREATE TABLE `customers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `accounting_id` BIGINT UNSIGNED NULL COMMENT 'شناسه حسابداری',
  `shop_name` VARCHAR(150) NULL COMMENT 'نام فروشگاه',
  `owner_name` VARCHAR(150) NOT NULL COMMENT 'نام صاحب فروشگاه',
  `shop_image` VARCHAR(150) NULL COMMENT 'تصویر فروشگاه',
  `national_code` VARCHAR(11) DEFAULT NULL COMMENT 'کد ملی / شناسه ملی',
  `economic_code` VARCHAR(12) DEFAULT NULL COMMENT 'کد اقتصادی',
  `customer_category_id` BIGINT UNSIGNED NOT NULL COMMENT 'دسته‌بندی مشتری',
  `birthday` DATE DEFAULT NULL COMMENT 'تاریخ تولد',
  `mobile` VARCHAR(15) NOT NULL COMMENT 'تلفن همراه اصلی',
  `phone` VARCHAR(15) DEFAULT NULL COMMENT 'تلفن ثابت',
  `phone_verified_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'تایید شماره تلفن',
  `alternative_mobile` VARCHAR(15) DEFAULT NULL COMMENT 'تلفن همراه دوم/رابط',
  `password` VARCHAR(255) DEFAULT NULL COMMENT 'رمز عبور',
  `province_id` INT DEFAULT NULL COMMENT 'کد استان از جدول provinces_list',
  `city_id` INT DEFAULT NULL COMMENT 'کد شهر از جدول provinces_cities',
  `address` TEXT NULL COMMENT 'آدرس دقیق پستی جهت توزیع',
  `postal_code` VARCHAR(10) DEFAULT NULL COMMENT 'کد پستی ۱۰ رقمی',
  `latitude` DECIMAL(10, 8) DEFAULT NULL COMMENT 'عرض جغرافیایی',
  `longitude` DECIMAL(11, 8) DEFAULT NULL COMMENT 'طول جغرافیایی',
  `zone_id` INT DEFAULT NULL COMMENT 'کد زون/مسیر توزیع متصل به جدول zones',
  `visitor_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'ویزیتور اختصاص داده شده',
  `visit_days` VARCHAR(50) DEFAULT NULL COMMENT 'روزهای ویزیت',
  `delivery_note` VARCHAR(250) DEFAULT NULL COMMENT 'توضیحات و راهنمای راننده پخش',
  `payment_type` ENUM('cash', 'credit', 'cheque', 'both') DEFAULT 'cash' COMMENT 'روش پرداخت مجاز',
  `credit_limit` DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'سقف اعتبار ریالی',
  `settlement_days` INT DEFAULT 0 COMMENT 'تعداد روزهای مهلت تسویه',
  `customer_grade` ENUM('A', 'B', 'C') DEFAULT 'C' COMMENT 'رتبه‌بندی مشتری',
  `has_company_equipment` TINYINT(1) DEFAULT 0 COMMENT 'آیا یخچال/استند امانی دارد؟',
  `equipment_note` VARCHAR(250) DEFAULT NULL COMMENT 'شرح تجهیزات امانی تحویل داده شده',
  `profile_status` ENUM('incomplete', 'completed') DEFAULT 'incomplete' COMMENT 'وضعیت تکمیل پرونده',
  `status` ENUM('pending','active', 'inactive', 'suspended') DEFAULT 'active' COMMENT 'وضعیت فعالیت در سیستم',
  `created_by` BIGINT UNSIGNED DEFAULT NULL COMMENT 'کاربر ثبت‌کننده مشتری',
  `notes` TEXT NULL COMMENT 'سایر توضیحات',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_customer_mobile` (`mobile`),
  CONSTRAINT `fk_customer_category` FOREIGN KEY (`customer_category_id`) REFERENCES `customers_cat`(`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_customer_province` FOREIGN KEY (`province_id`) REFERENCES `provinces_list`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_customer_city` FOREIGN KEY (`city_id`) REFERENCES `provinces_cities`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_customer_zone` FOREIGN KEY (`zone_id`) REFERENCES `zones`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_customer_visitor` FOREIGN KEY (`visitor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_customer_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_customer_profile_status` (`profile_status`),
  INDEX `idx_customer_national_code` (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ۱۱. جدول مشتریان (نسخه اصلاح شده با موبایل یکتا و ارتباط با مسیرها)

  



-- ۱۲. جدول تاریخچه انتساب بازاریاب‌ها به مشتری
CREATE TABLE customer_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    visitor_id BIGINT UNSIGNED NOT NULL,
    assigned_from DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    assigned_to DATETIME NULL,
    status ENUM('active','ended') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_customer_assignments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT fk_customer_assignments_visitor FOREIGN KEY (visitor_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_customer_assignments_customer (customer_id),
    INDEX idx_customer_assignments_visitor (visitor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ساختار CRM و چرخه عمر مشتریان
-- CRM: چرخه عمر مشتری، تاریخچه وضعیت، فعالیت‌ها و پیگیری‌ها

CREATE TABLE IF NOT EXISTS `customer_statuses` (
  `id` TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(40) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(30) NULL,
  `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

INSERT IGNORE INTO `customer_statuses`
  (`id`, `slug`, `name`, `color`, `sort_order`, `is_active`)
VALUES
  (1, 'lead',     'سرنخ',              'secondary', 10, 1),
  (2, 'potential','مشتری بالقوه',      'info',      20, 1),
  (3, 'customer', 'مشتری واقعی',       'success',   30, 1),
  (4, 'inactive', 'کم‌فعال / نیازمند پیگیری', 'warning', 40, 1),
  (5, 'lost',     'از دست رفته',       'danger',    50, 1);

ALTER TABLE `customers`
  ADD COLUMN `lifecycle_status_id` TINYINT UNSIGNED NULL
    COMMENT 'وضعیت مشتری در چرخه CRM' AFTER `status`,
  ADD COLUMN `lead_source` VARCHAR(50) NULL
    COMMENT 'منبع سرنخ: field_visit, referral, phone و...' AFTER `lifecycle_status_id`,
  ADD COLUMN `first_contact_at` DATETIME NULL AFTER `lead_source`,
  ADD COLUMN `first_purchase_at` DATETIME NULL AFTER `first_contact_at`,
  ADD COLUMN `last_activity_at` DATETIME NULL AFTER `first_purchase_at`,
  ADD COLUMN `next_follow_up_at` DATETIME NULL AFTER `last_activity_at`,
  ADD COLUMN `lost_at` DATETIME NULL AFTER `next_follow_up_at`,
  ADD COLUMN `lost_reason` VARCHAR(255) NULL AFTER `lost_at`;

UPDATE `customers`
SET `lifecycle_status_id` = 3
WHERE `lifecycle_status_id` IS NULL;

ALTER TABLE `customers`
  MODIFY COLUMN `lifecycle_status_id` TINYINT UNSIGNED NOT NULL DEFAULT 2
    COMMENT 'وضعیت مشتری در چرخه CRM',
  ADD INDEX `idx_customer_lifecycle_status` (`lifecycle_status_id`),
  ADD CONSTRAINT `fk_customer_lifecycle_status`
    FOREIGN KEY (`lifecycle_status_id`) REFERENCES `customer_statuses` (`id`)
    ON DELETE RESTRICT;

CREATE TABLE IF NOT EXISTS `customer_status_history` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id` BIGINT UNSIGNED NOT NULL,
  `old_status_id` TINYINT UNSIGNED NULL,
  `new_status_id` TINYINT UNSIGNED NOT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `reason` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_customer_status_history_customer`
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_status_history_old_status`
    FOREIGN KEY (`old_status_id`) REFERENCES `customer_statuses` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_customer_status_history_new_status`
    FOREIGN KEY (`new_status_id`) REFERENCES `customer_statuses` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_customer_status_history_user`
    FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  INDEX `idx_customer_status_history_customer` (`customer_id`, `created_at`),
  INDEX `idx_customer_status_history_new_status` (`new_status_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

CREATE TABLE IF NOT EXISTS `customer_activities` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id` BIGINT UNSIGNED NOT NULL,
  `performed_by` BIGINT UNSIGNED NOT NULL,
  `activity_type` VARCHAR(30) NOT NULL
    COMMENT 'visit, call, happy_call, sms, whatsapp, note, purchase',
  `activity_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `result` VARCHAR(100) NULL,
  `note` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_customer_activities_customer`
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_activities_user`
    FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  INDEX `idx_customer_activities_customer` (`customer_id`, `activity_at`),
  INDEX `idx_customer_activities_performed_by` (`performed_by`, `activity_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

CREATE TABLE IF NOT EXISTS `follow_ups` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id` BIGINT UNSIGNED NOT NULL,
  `assigned_to` BIGINT UNSIGNED NOT NULL
    COMMENT 'بازاریاب یا پرسنل دفتر انجام‌دهنده پیگیری',
  `due_at` DATETIME NOT NULL,
  `follow_up_type` VARCHAR(30) NOT NULL DEFAULT 'call'
    COMMENT 'visit, call, happy_call, sms, whatsapp, other',
  `status` ENUM('pending', 'done', 'cancelled') NOT NULL DEFAULT 'pending',
  `result` VARCHAR(100) NULL,
  `note` TEXT NULL,
  `completed_at` DATETIME NULL,
  `completed_by` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_follow_ups_customer`
    FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_follow_ups_assigned_to`
    FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_follow_ups_completed_by`
    FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_follow_ups_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  INDEX `idx_follow_ups_due_status` (`due_at`, `status`),
  INDEX `idx_follow_ups_assigned_due` (`assigned_to`, `due_at`, `status`),
  INDEX `idx_follow_ups_customer` (`customer_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

INSERT INTO `customer_status_history`
  (`customer_id`, `old_status_id`, `new_status_id`, `changed_by`, `reason`)
SELECT
  c.`id`, NULL, c.`lifecycle_status_id`, c.`created_by`, 'مهاجرت اولیه وضعیت CRM'
FROM `customers` c
LEFT JOIN `customer_status_history` h
  ON h.`customer_id` = c.`id`
WHERE h.`id` IS NULL;