-- ==========================================
-- سیستم اتوماسیون قراردادها - دیتابیس مستقل
-- نسخه: 2.0.0
-- ==========================================


-- --------------------------------------------------------
-- جدول تنظیمات سیستم (جایگزین wp_options)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_settings` (
  `key_name` VARCHAR(191) NOT NULL,
  `value` LONGTEXT,
  PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول نقش‌ها و دسترسی‌ها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_roles` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `permissions` LONGTEXT,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول طرف حساب‌ها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_counterparties` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) NOT NULL,
  `company` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `account_number` VARCHAR(50) DEFAULT NULL,
  `created_by` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول رانندگان
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_drivers` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول قراردادها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_contracts` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `template_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `counterparty_id` BIGINT(20) UNSIGNED NOT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `content` LONGTEXT,
  `meta_data` LONGTEXT DEFAULT NULL,
  `contract_number` VARCHAR(50) DEFAULT NULL,
  `tracking_number` VARCHAR(10) DEFAULT NULL,
  `total_amount` DECIMAL(15,2) DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT NULL,
  `contract_date` DATE DEFAULT NULL,
  `watermark_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `tracking_number` (`tracking_number`),
  KEY `template_id` (`template_id`),
  KEY `counterparty_id` (`counterparty_id`),
  KEY `created_by` (`created_by`),
  KEY `watermark_id` (`watermark_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول قالب‌های قرارداد
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_contract_templates` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `header_content` LONGTEXT,
  `content` LONGTEXT,
  `footer_content` LONGTEXT,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول فیلدهای سفارشی
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_custom_fields` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `field_label` VARCHAR(255) NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `field_type` VARCHAR(50) NOT NULL,
  `value_source` VARCHAR(20) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `field_key` (`field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول متای قالب قرارداد
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_contract_template_meta` (
  `meta_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `template_id` BIGINT(20) NOT NULL,
  `meta_key` VARCHAR(255) NOT NULL,
  `meta_value` LONGTEXT,
  PRIMARY KEY  (`meta_id`),
  KEY `template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول لاگ فعالیت‌ها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_activity_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `user_name` VARCHAR(255) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `object_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `object_type` VARCHAR(50) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول وسایل نقلیه
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_vehicles` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `counterparty_id` BIGINT(20) UNSIGNED NOT NULL,
  `plate_number` VARCHAR(50) NOT NULL,
  `model` VARCHAR(100) DEFAULT NULL,
  `capacity` INT(11) DEFAULT NULL,
  `driver_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_by` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `plate_number` (`plate_number`),
  KEY `counterparty_id` (`counterparty_id`),
  KEY `driver_id` (`driver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول بارها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_loads` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `purchase_amount` INT(11) NOT NULL,
  `customer_id` BIGINT(20) UNSIGNED NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `final_amount` DECIMAL(15,2) DEFAULT NULL,
  `payment_details` TEXT DEFAULT NULL,
  `purchase_date` DATE DEFAULT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `customer_id` (`customer_id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول ماشین‌های مرتبط با بار
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_load_vehicles` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `load_id` BIGINT(20) UNSIGNED NOT NULL,
  `vehicle_id` BIGINT(20) UNSIGNED NOT NULL,
  `driver_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'sent',
  `amount` DECIMAL(15,2) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `unloaded_at` DATETIME DEFAULT NULL,
  PRIMARY KEY  (`id`),
  KEY `load_id` (`load_id`),
  KEY `vehicle_id` (`vehicle_id`),
  KEY `driver_id` (`driver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول لاگ تغییرات وضعیت بار
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_load_status_logs` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `load_vehicle_id` BIGINT(20) UNSIGNED NOT NULL,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `status_from` VARCHAR(50) DEFAULT NULL,
  `status_to` VARCHAR(50) NOT NULL,
  `log_details` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `load_vehicle_id` (`load_vehicle_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول فاکتورها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_invoices` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `vehicle_plate` VARCHAR(50) DEFAULT NULL,
  `driver_name` VARCHAR(255) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `total_amount` DECIMAL(15,2) NOT NULL,
  `attachments` LONGTEXT,
  `status` VARCHAR(20) NOT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول نامه‌ها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_letters` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `letter_number` VARCHAR(100) NOT NULL,
  `letter_date` DATE NOT NULL,
  `content` LONGTEXT,
  `meta_data` LONGTEXT,
  `recipient` TEXT DEFAULT NULL,
  `signer_name` VARCHAR(255) DEFAULT NULL,
  `template_id` BIGINT(20) DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول قالب‌های نامه
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_letter_templates` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `header_content` LONGTEXT,
  `content` LONGTEXT,
  `footer_content` LONGTEXT,
  `main_font` VARCHAR(100) DEFAULT NULL,
  `variable_font` VARCHAR(100) DEFAULT NULL,
  `watermark_image_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `watermark_opacity` FLOAT DEFAULT NULL,
  `watermark_position` VARCHAR(50) DEFAULT NULL,
  `watermark_size` VARCHAR(20) DEFAULT NULL,
  `watermark_rotation` INT(11) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول امضاکنندگان
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_signers` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `position` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول لیجرها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_ledgers` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `owner_name` VARCHAR(255) NOT NULL,
  `plate_number` VARCHAR(50) NOT NULL,
  `account_number` VARCHAR(50) DEFAULT NULL,
  `month` VARCHAR(20) NOT NULL,
  `year` INT(4) NOT NULL,
  `oil_company_amount` DECIMAL(15,2) NOT NULL,
  `tonnage_deduction` DECIMAL(15,2) DEFAULT 0,
  `other_deduction` DECIMAL(15,2) DEFAULT 0,
  `khosh_bill` DECIMAL(15,2) DEFAULT NULL,
  `zahedan_bill` DECIMAL(15,2) DEFAULT NULL,
  `income_tax` DECIMAL(15,2) DEFAULT NULL,
  `zahedan_free_cargo` DECIMAL(15,2) DEFAULT NULL,
  `khosh_free_cargo` DECIMAL(15,2) DEFAULT NULL,
  `fines` DECIMAL(15,2) DEFAULT NULL,
  `previous_debt` DECIMAL(15,2) DEFAULT NULL,
  `commission` DECIMAL(15,2) DEFAULT NULL,
  `payable_amount` DECIMAL(15,2) NOT NULL,
  `payment_amount` DECIMAL(15,2) DEFAULT NULL,
  `payment_date` DATE DEFAULT NULL,
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `tracking_number` VARCHAR(100) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول واترمارک‌ها
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_watermarks` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `type` VARCHAR(10) NOT NULL,
  `content` TEXT,
  `opacity` FLOAT DEFAULT NULL,
  `rotation` INT(11) DEFAULT NULL,
  `size` VARCHAR(20) DEFAULT NULL,
  `font_weight` VARCHAR(10) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول دوره‌های بارنامه
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_waybill_periods` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `counterparty_id` bigint(20) unsigned NOT NULL,
    `vehicle_id` bigint(20) unsigned NOT NULL,
    `driver_name` varchar(255) DEFAULT NULL,
    `start_date` date NOT NULL,
    `end_date` date NOT NULL,
    `created_by` bigint(20) unsigned NOT NULL,
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `counterparty_id` (`counterparty_id`),
    KEY `vehicle_id` (`vehicle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول آیتم‌های بارنامه
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_waybill_items` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `period_id` bigint(20) unsigned NOT NULL,
    `reference_number` varchar(100) DEFAULT NULL,
    `terminal_waybill_number` varchar(100) DEFAULT NULL,
    `sending_date` date DEFAULT NULL,
    `product_type` varchar(100) DEFAULT NULL,
    `litre_amount` int(11) DEFAULT NULL,
    `shortage_amount` int(11) DEFAULT NULL,
    `origin` varchar(255) DEFAULT NULL,
    `destination` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `period_id` (`period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول حسابرسی
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_auditing` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type` VARCHAR(50) DEFAULT NULL,
    `event_date` DATE DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `amount_in` DECIMAL(15,2) DEFAULT 0,
    `amount_out` DECIMAL(15,2) DEFAULT 0,
    `balance` DECIMAL(15,2) DEFAULT 0,
    `created_by` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    KEY `event_type` (`event_type`),
    KEY `event_date` (`event_date`),
    KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول حساب و کتاب رانندگان
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_driver_accounts` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `driver_name` VARCHAR(255) NOT NULL,
    `vehicle_plate` VARCHAR(50) NOT NULL,
    `owner_national_id` VARCHAR(20) NOT NULL,
    `start_date` VARCHAR(20) NOT NULL,
    `end_date` VARCHAR(20) NOT NULL,
    `service_count` INT(11) DEFAULT 0,
    `service_cost` DECIMAL(15,2) DEFAULT 0,
    `repair_cost` DECIMAL(15,2) DEFAULT 0,
    `diesel_sales` DECIMAL(15,2) DEFAULT 0,
    `costs_description` TEXT DEFAULT NULL,
    `deposits` LONGTEXT DEFAULT NULL,
    `driver_salary` DECIMAL(15,2) DEFAULT 0,
    `driver_balance` DECIMAL(15,2) DEFAULT 0,
    `vehicle_balance` DECIMAL(15,2) DEFAULT 0,
    `final_description` TEXT DEFAULT NULL,
    `created_by` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- --------------------------------------------------------
-- جدول بارهای آزاد
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cmr_free_loads` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id` BIGINT(20) UNSIGNED NOT NULL,
    `driver_id` BIGINT(20) UNSIGNED DEFAULT NULL,
    `amount` DECIMAL(15,2) NOT NULL,
    `commission_rate` DECIMAL(5,2) DEFAULT 5.00,
    `commission_amount` DECIMAL(15,2) DEFAULT 0,
    `driver_share` DECIMAL(15,2) DEFAULT 0,
    `origin` VARCHAR(255) DEFAULT NULL,
    `destination` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `status` VARCHAR(50) DEFAULT 'pending',
    `unloaded_at` DATETIME DEFAULT NULL,
    `created_by` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_id` (`vehicle_id`),
    KEY `driver_id` (`driver_id`),
    KEY `status` (`status`),
    KEY `unloaded_at` (`unloaded_at`),
    KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;


-- ==========================================
-- درج داده‌های پیش‌فرض (Seed Data)
-- ==========================================

-- ایجاد نقش مدیر کل (دسترسی کامل)
INSERT INTO `cmr_roles` (`id`, `name`, `permissions`, `created_at`) VALUES
(1, 'مدیر کل', '["view_dashboard","view_contracts","add_contracts","edit_contracts","delete_contracts","view_counterparties","add_counterparties","edit_counterparties","delete_counterparties","view_vehicles","add_vehicles","edit_vehicles","delete_vehicles","view_ledgers","add_ledgers","edit_ledgers","delete_ledgers","view_auditing","add_auditing","edit_auditing","delete_auditing","view_waybills","add_waybills","edit_waybills","delete_waybills","view_invoices","add_invoices","edit_invoices","delete_invoices","view_loads","add_loads","edit_loads","delete_loads","view_reports","view_letters","add_letters","delete_letters","manage_settings"]', NOW());


