-- ANBT canonical schema reference generated from anode_battery (6).sql
-- Data statements intentionally excluded.
-- Source snapshot: 2026-08-13.

DELIMITER $$
CREATE  PROCEDURE `sp_calculate_used_battery_credit` (IN `p_amperage` INT UNSIGNED, OUT `p_credit` BIGINT UNSIGNED, OUT `p_rate` INT UNSIGNED)   BEGIN
  SELECT rate_per_amp INTO p_rate
  FROM used_battery_rates WHERE is_current = 1 LIMIT 1;
  IF p_rate IS NULL THEN
    
    SELECT CAST(value AS UNSIGNED) INTO p_rate
    FROM settings WHERE setting_group = 'used_battery' AND setting_key = 'rate_per_amp' LIMIT 1;
  END IF;
  IF p_rate IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No active used battery rate found';
  END IF;
  SET p_credit = p_amperage * p_rate;
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `sp_database_health_check` ()   BEGIN
  
  SELECT 'ACCOUNTING_BALANCE' AS check_name,
    CASE WHEN SUM(debit) = SUM(credit) THEN 'OK' ELSE 'FAIL' END AS status,
    SUM(debit) AS total_debit, SUM(credit) AS total_credit,
    SUM(debit) - SUM(credit) AS difference
  FROM rial_ledger rl
  INNER JOIN accounting_vouchers av ON av.uuid = rl.voucher_id
  WHERE av.status = 'posted';

  
  SELECT 'NEGATIVE_STOCK' AS check_name,
    COUNT(*) AS count, GROUP_CONCAT(id SEPARATOR ',') AS product_ids
  FROM products WHERE stock_quantity < 0;

  
  SELECT 'STALE_PENDING_ORDERS' AS check_name, COUNT(*) AS count
  FROM orders
  WHERE status = 'pending_payment'
    AND created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR)
    AND (expires_at IS NULL OR expires_at > NOW());

  
  SELECT 'LOW_STOCK_PRODUCTS' AS check_name, COUNT(*) AS count
  FROM products
  WHERE stock_quantity <= min_stock_alert AND is_active = 1 AND deleted_at IS NULL;

  
  SELECT 'EXPIRED_OTPS' AS check_name, COUNT(*) AS count
  FROM otp_codes WHERE expires_at < NOW() AND verified_at IS NULL;

  
  SELECT 'OVERUSED_COUPONS' AS check_name,
    COUNT(*) AS count, GROUP_CONCAT(code SEPARATOR ',') AS coupon_codes
  FROM coupons WHERE usage_limit IS NOT NULL AND used_count > usage_limit;
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `sp_generate_order_number` (OUT `p_order_number` VARCHAR(20))   BEGIN
  DECLARE v_date_part VARCHAR(8);
  DECLARE v_seq INT;
  SET v_date_part = DATE_FORMAT(NOW(), '%Y%m%d');
  SELECT COALESCE(MAX(CAST(SUBSTRING(order_number, 13) AS UNSIGNED)), 0) + 1
  INTO v_seq
  FROM orders WHERE order_number LIKE CONCAT('ORD-', v_date_part, '-%');
  SET p_order_number = CONCAT('ORD-', v_date_part, '-', LPAD(v_seq, 4, '0'));
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `sp_generate_warranty_number` (OUT `p_warranty_no` VARCHAR(20))   BEGIN
  DECLARE v_date_part VARCHAR(8);
  DECLARE v_seq INT;
  SET v_date_part = DATE_FORMAT(NOW(), '%Y%m%d');
  SELECT COALESCE(MAX(CAST(SUBSTRING(warranty_no, 13) AS UNSIGNED)), 0) + 1
  INTO v_seq
  FROM warranties WHERE warranty_no LIKE CONCAT('WRN-', v_date_part, '-%');
  SET p_warranty_no = CONCAT('WRN-', v_date_part, '-', LPAD(v_seq, 4, '0'));
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `_add_fk_if_not_exists` (IN `p_table` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IN `p_constraint` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IN `p_sql` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)   BEGIN
  DECLARE v_count INT;
  SELECT COUNT(*) INTO v_count
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = p_table COLLATE utf8mb4_unicode_ci
    AND CONSTRAINT_NAME = p_constraint COLLATE utf8mb4_unicode_ci
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
  IF v_count = 0 THEN
    SET @_fk_sql = p_sql;
    PREPARE _fk_stmt FROM @_fk_sql;
    EXECUTE _fk_stmt;
    DEALLOCATE PREPARE _fk_stmt;
  END IF;
END$$
DELIMITER ;

CREATE TABLE `accounting_vouchers` (
  `id` int(10) UNSIGNED NOT NULL,
  `uuid` char(36) NOT NULL,
  `voucher_no` varchar(20) DEFAULT NULL,
  `type` varchar(50) NOT NULL COMMENT 'e.g., receipt, payment, purchase_used',
  `date` date NOT NULL,
  `amount` bigint(20) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `ledger_type` varchar(50) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `fiscal_period_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('draft','posted','voided') NOT NULL DEFAULT 'draft',
  `posted_at` datetime DEFAULT NULL,
  `posted_by` int(10) UNSIGNED DEFAULT NULL,
  `voided_at` datetime DEFAULT NULL,
  `voided_by` int(10) UNSIGNED DEFAULT NULL,
  `void_reason` varchar(255) DEFAULT NULL,
  `transaction_uuid` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `admin_permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `permission_id` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `admin_refresh_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `jti` varchar(32) NOT NULL COMMENT 'JWT ID',
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `admin_sessions` (
  `id` varchar(128) NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `payload` text NOT NULL,
  `last_activity` int(10) UNSIGNED NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `admin_users` (
  `id` int(10) UNSIGNED NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `role` enum('super_admin','admin','operator','warehouse','accountant') NOT NULL DEFAULT 'operator',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `password_changed_at` datetime DEFAULT NULL,
  `failed_login_count` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `two_factor_secret` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `amperages` (
  `id` int(10) UNSIGNED NOT NULL,
  `amperage` int(10) UNSIGNED NOT NULL,
  `mandatory_amperage` int(11) DEFAULT NULL,
  `label` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `amperage_standards` (
  `id` int(10) UNSIGNED NOT NULL,
  `amperage` int(10) UNSIGNED NOT NULL,
  `label` varchar(50) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ampere_accounts` (
  `code` varchar(32) NOT NULL,
  `name_fa` varchar(150) NOT NULL,
  `account_type` enum('asset','liability','contra','expense') NOT NULL,
  `normal_balance` enum('debit','credit') NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ampere_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `transaction_uuid` char(36) NOT NULL,
  `line_no` smallint(5) UNSIGNED NOT NULL,
  `account_code` varchar(32) NOT NULL,
  `party_id` int(10) UNSIGNED DEFAULT NULL,
  `party_type` enum('customer','partner','supplier','factory','internal') DEFAULT NULL,
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `debit_ampere` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `credit_ampere` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `ampere_qty` int(10) UNSIGNED NOT NULL,
  `rate_snapshot` bigint(20) UNSIGNED DEFAULT NULL,
  `rate_id` int(10) UNSIGNED DEFAULT NULL,
  `rate_version` int(10) UNSIGNED DEFAULT NULL,
  `rate_type` enum('ampere_credit','used_battery') DEFAULT NULL,
  `rial_amount` bigint(20) DEFAULT NULL,
  `balance_after` int(11) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
);

CREATE TABLE `ampere_rial_reconciliation` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `run_id` char(36) NOT NULL,
  `transaction_uuid` char(36) DEFAULT NULL,
  `status` enum('MATCHED','VARIANCE_ACCEPTED','MISSING_RIAL','MISSING_AMPERE','UNBALANCED_RIAL','UNBALANCED_AMPERE','AMOUNT_MISMATCH','RATE_MISMATCH','DUPLICATE','ORPHAN_LINE','LEGACY_EXCEPTION') NOT NULL,
  `ampere_rial_value` bigint(20) DEFAULT NULL,
  `rial_value` bigint(20) DEFAULT NULL,
  `difference` bigint(20) DEFAULT NULL,
  `detail` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`detail`)),
  `detected_at` datetime NOT NULL DEFAULT current_timestamp(),
  `resolved_at` datetime DEFAULT NULL,
  `resolved_by` int(10) UNSIGNED DEFAULT NULL,
  `resolution_tx` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `api_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `token_prefix` varchar(8) NOT NULL,
  `abilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`abilities`)),
  `last_used_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `user_type` enum('customer','admin') DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` int(10) UNSIGNED DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `battery_case_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL COMMENT 'e.g., L1, L2, D26',
  `standard` enum('DIN','JIS','BCI') DEFAULT 'DIN' COMMENT '??????????????????: ??????????????/??????????/????????????????',
  `length_mm` int(10) UNSIGNED DEFAULT NULL COMMENT '?????? ???? ?????????????????',
  `width_mm` int(10) UNSIGNED DEFAULT NULL COMMENT '?????? ???? ?????????????????',
  `height_mm` int(10) UNSIGNED DEFAULT NULL COMMENT '???????????? ???? ?????????????????',
  `terminal_type` enum('A','B','SIDE') DEFAULT 'A' COMMENT '?????? ??????????????: A=?????????????? ?????????? B=?????????? ?????????? SIDE=??????????',
  `is_tall_base` tinyint(1) DEFAULT 1 COMMENT '???????? ????????=TRUE?? ???????? ??????????=FALSE',
  `display_order` int(11) DEFAULT 0 COMMENT '?????????? ??????????'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `battery_recommendation_rules` (
  `id` int(10) UNSIGNED NOT NULL,
  `rule_code` varchar(50) NOT NULL COMMENT 'Unique rule identifier',
  `rule_name_fa` varchar(100) NOT NULL COMMENT 'Persian name for display',
  `rule_description` text DEFAULT NULL COMMENT 'Detailed rule description',
  `priority` int(11) DEFAULT 0 COMMENT 'Higher priority rules applied first',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brands` (
  `id` int(10) UNSIGNED NOT NULL,
  `factory_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brand_ampere_rates` (
  `id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED NOT NULL COMMENT 'FK to brands.id',
  `rate_type` enum('retail','wholesale','used_battery','ampere_credit') NOT NULL DEFAULT 'ampere_credit' COMMENT 'Type of rate: retail, wholesale, used_battery, or ampere_credit',
  `amperage_id` int(10) UNSIGNED DEFAULT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `rate_per_ampere` decimal(14,4) NOT NULL COMMENT 'Rial amount per 1 ampere',
  `effective_from` datetime NOT NULL COMMENT 'When this rate becomes active',
  `effective_to` datetime DEFAULT NULL COMMENT 'NULL = currently active; set by trigger when new rate inserted',
  `is_current` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = active rate, 0 = archived',
  `version` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `notes` varchar(500) DEFAULT NULL COMMENT 'Admin note for this rate change',
  `created_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK to admin_users.id',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `rate_unit` enum('rial') NOT NULL DEFAULT 'rial'
);

DELIMITER $$
CREATE TRIGGER `trg_bar_duplicate_guard` BEFORE INSERT ON `brand_ampere_rates` FOR EACH ROW BEGIN
    IF NEW.product_id IS NOT NULL AND NEW.amperage_id IS NOT NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'INVALID RATE: product_id and amperage_id cannot both be set simultaneously.';
    END IF;

    IF NEW.is_current = 1 AND EXISTS (
        SELECT 1 FROM brand_ampere_rates
        WHERE brand_id    = NEW.brand_id
          AND rate_type   = NEW.rate_type
          AND amperage_id <=> NEW.amperage_id
          AND product_id  <=> NEW.product_id
          AND is_current  = 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'DUPLICATE ACTIVE RATE: A current rate already exists for this combination.';
    END IF;
END$$
DELIMITER ;

CREATE TABLE `business_transactions` (
  `uuid` char(36) NOT NULL,
  `tx_no` varchar(32) NOT NULL,
  `event_type` varchar(64) NOT NULL,
  `posting_class` enum('SALE','SETTLEMENT','PURCHASE','RETURN','WARRANTY','PAYMENT','ADJUSTMENT','INTERNAL_MOVE','LOSS') NOT NULL,
  `valuation_bridge` enum('STRICT','VARIANCE','NONE') NOT NULL,
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(10) UNSIGNED NOT NULL,
  `party_id` int(10) UNSIGNED DEFAULT NULL,
  `party_type` enum('customer','partner','supplier','factory','internal') DEFAULT NULL,
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `fiscal_period_id` int(10) UNSIGNED NOT NULL,
  `idempotency_key` varchar(255) NOT NULL,
  `status` enum('draft','posted','reversed') NOT NULL DEFAULT 'draft',
  `reverses_uuid` char(36) DEFAULT NULL,
  `reversed_by_uuid` char(36) DEFAULT NULL,
  `total_rial` bigint(20) NOT NULL DEFAULT 0,
  `total_ampere` int(11) NOT NULL DEFAULT 0,
  `legacy_source` varchar(64) DEFAULT NULL,
  `legacy_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `posted_at` datetime DEFAULT NULL
);

CREATE TABLE `carts` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cart_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `cart_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_amperage` int(10) UNSIGNED DEFAULT NULL,
  `used_battery_amperage_2` int(10) UNSIGNED DEFAULT NULL,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `price_snapshot` bigint(20) UNSIGNED NOT NULL COMMENT 'Price at time of adding',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cart_item_used_batteries` (
  `id` int(10) UNSIGNED NOT NULL,
  `cart_item_id` int(10) UNSIGNED NOT NULL,
  `slot_number` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `amperage` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `car_brands` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL COMMENT 'Brand name in Persian',
  `slug` varchar(100) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `car_compatibility` (
  `id` int(10) UNSIGNED NOT NULL,
  `model_id` int(10) UNSIGNED NOT NULL,
  `amperage` int(10) UNSIGNED NOT NULL,
  `case_type` varchar(20) DEFAULT NULL,
  `polarity` varchar(10) DEFAULT NULL COMMENT 'L or R',
  `is_factory` tinyint(1) NOT NULL DEFAULT 0,
  `notes` text DEFAULT NULL,
  `has_year_variant` tinyint(1) NOT NULL DEFAULT 0,
  `recommendation_type` enum('factory','upgrade','compatible') NOT NULL DEFAULT 'factory',
  `is_upgrade` tinyint(1) NOT NULL DEFAULT 0,
  `is_l3_compatible` tinyint(1) NOT NULL DEFAULT 0,
  `needs_year_check` tinyint(1) NOT NULL DEFAULT 0,
  `smart_factory_applied` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `car_models` (
  `id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL COMMENT 'Model name in Persian',
  `slug` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `case_type_amperage_range` (
  `id` int(10) UNSIGNED NOT NULL,
  `case_type_id` int(10) UNSIGNED NOT NULL,
  `min_amperage` int(10) UNSIGNED NOT NULL COMMENT '?????????? ????????',
  `max_amperage` int(10) UNSIGNED NOT NULL COMMENT '???????????? ????????',
  `common_amperages` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '?????????????? ???????? [35,45,50]' CHECK (json_valid(`common_amperages`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `case_type_compatibility` (
  `id` int(10) UNSIGNED NOT NULL,
  `source_case_type_id` int(10) UNSIGNED NOT NULL COMMENT '???????? ???????? ??????????',
  `target_case_type_id` int(10) UNSIGNED NOT NULL COMMENT '???????? ???????? ??????',
  `fit_type` enum('exact','smaller','larger','adapter_needed') DEFAULT 'exact' COMMENT '?????? ??????????',
  `notes_fa` varchar(255) DEFAULT NULL COMMENT '?????????????? ??????????',
  `is_recommended` tinyint(1) DEFAULT 1 COMMENT '?????????????? ????????'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `case_type_upgrades` (
  `id` int(10) UNSIGNED NOT NULL,
  `case_type_id` int(10) UNSIGNED NOT NULL,
  `supports_amperage_id` int(10) UNSIGNED NOT NULL,
  `is_default_upgrade` tinyint(1) DEFAULT 0 COMMENT 'Primary upgrade option',
  `priority` int(11) DEFAULT 0 COMMENT 'Display order',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `is_combinable` tinyint(1) NOT NULL DEFAULT 1,
  `shipping_type` enum('store_only','nationwide_cod') NOT NULL DEFAULT 'nationwide_cod',
  `max_per_order` int(10) UNSIGNED DEFAULT NULL COMMENT 'NULL = Unlimited',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `chart_of_accounts` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(20) NOT NULL,
  `name_fa` varchar(100) NOT NULL,
  `name_en` varchar(100) DEFAULT NULL,
  `account_type` enum('asset','liability','equity','revenue','expense') NOT NULL,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `level` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `is_detail` tinyint(1) NOT NULL DEFAULT 1,
  `normal_balance` enum('debit','credit') NOT NULL,
  `currency` enum('rial','ampere') NOT NULL DEFAULT 'rial',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cities` (
  `id` int(10) UNSIGNED NOT NULL,
  `province_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `delivery_available` tinyint(1) NOT NULL DEFAULT 1,
  `delivery_fee` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Override delivery fee for this city'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupons` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(20) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL,
  `value` bigint(20) UNSIGNED NOT NULL COMMENT 'Percentage (0-100) or Rial amount',
  `min_order_amount` bigint(20) UNSIGNED DEFAULT NULL,
  `max_discount` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Max discount for percentage type',
  `usage_limit` int(10) UNSIGNED DEFAULT NULL COMMENT 'Total usage limit',
  `per_user_limit` int(10) UNSIGNED DEFAULT NULL COMMENT 'Per user limit',
  `used_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupon_usages` (
  `id` int(10) UNSIGNED NOT NULL,
  `coupon_id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `discount_amount` bigint(20) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER $$
CREATE TRIGGER `trg_coupon_usage_after_delete` AFTER DELETE ON `coupon_usages` FOR EACH ROW BEGIN
  UPDATE coupons SET used_count = GREATEST(used_count - 1, 0) WHERE id = OLD.coupon_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `trg_coupon_usage_after_insert` AFTER INSERT ON `coupon_usages` FOR EACH ROW BEGIN
  UPDATE coupons SET used_count = used_count + 1 WHERE id = NEW.coupon_id;
END$$
DELIMITER ;

CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `mobile` varchar(11) NOT NULL COMMENT 'Format: 09xxxxxxxxx',
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `national_code` varchar(10) DEFAULT NULL COMMENT 'Iranian National ID',
  `employee_code` varchar(20) DEFAULT NULL COMMENT '┌®Ï» ┘à┘åÏ¡ÏÁÏ▒ Ï¿┘ç ┘üÏ▒Ï» ┘ç┘à┌®ÏºÏ▒ (EMP-XXXX) - Ï¬┘å┘çÏº Ï¿Ï▒Ïº█î ┘ç┘à┌®ÏºÏ▒Ïº┘å█î ┌®┘ç user_type=partner Ï»ÏºÏ▒┘åÏ»',
  `accepts_used_battery` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Partner purchases with used battery trade-in by default',
  `email` varchar(100) DEFAULT NULL,
  `user_type` enum('customer','partner') NOT NULL DEFAULT 'customer',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `cod_allowed` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Can use Cash on Delivery',
  `cod_blocked_reason` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `last_login_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_addresses` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `province_id` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(50) NOT NULL COMMENT 'e.g., خانه، محل کار',
  `receiver_name` varchar(100) NOT NULL,
  `receiver_mobile` varchar(11) NOT NULL,
  `province` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `postal_code` varchar(10) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_grade_tiers` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `min_amperage` int(10) UNSIGNED NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_notes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL COMMENT 'کدام ادمین این یادداشت را نوشته',
  `note` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL COMMENT 'soft delete'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_reminders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `created_by_admin_id` int(10) UNSIGNED NOT NULL,
  `assigned_to_admin_id` int(10) UNSIGNED NOT NULL COMMENT 'کدام ادمین باید یادآوری را ببیند',
  `remind_at` datetime NOT NULL COMMENT 'زمان دقیق یادآوری',
  `message` varchar(500) NOT NULL,
  `status` enum('pending','done','dismissed') NOT NULL DEFAULT 'pending',
  `notified_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `defect_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(20) NOT NULL,
  `title` varchar(100) NOT NULL COMMENT 'Persian title',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `factories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `financial_baseline` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `captured_at` datetime NOT NULL DEFAULT current_timestamp(),
  `table_name` varchar(80) NOT NULL,
  `row_count` bigint(20) DEFAULT NULL,
  `debit_total` decimal(30,0) DEFAULT NULL,
  `credit_total` decimal(30,0) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `fiscal_periods` (
  `id` int(10) UNSIGNED NOT NULL,
  `fiscal_year` smallint(5) UNSIGNED NOT NULL,
  `period_number` tinyint(3) UNSIGNED NOT NULL,
  `period_type` enum('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('open','closed','locked') NOT NULL DEFAULT 'open',
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `idempotency_records` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `idempotency_key` varchar(255) NOT NULL,
  `endpoint` varchar(100) NOT NULL,
  `request_hash` char(64) NOT NULL,
  `response_status_code` smallint(5) UNSIGNED NOT NULL,
  `response_payload` longtext NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `image_processing_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `uploaded_file_id` bigint(20) UNSIGNED NOT NULL,
  `status` enum('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  `thumbnail_path` varchar(500) DEFAULT NULL,
  `error_message` varchar(1000) DEFAULT NULL,
  `retry_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inventory_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `type` varchar(50) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` bigint(20) UNSIGNED DEFAULT 0,
  `balance_after` int(11) NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL COMMENT 'e.g., order, manual',
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inventory_used` (
  `id` int(11) NOT NULL,
  `amperage` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 0,
  `total_ampere` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `login_attempts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `mobile` varchar(11) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_type` enum('customer','admin') NOT NULL DEFAULT 'customer',
  `status` enum('success','failed_otp','failed_password','blocked','expired') NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lookup_admin_roles` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `label_fa` varchar(50) NOT NULL,
  `label_en` varchar(50) NOT NULL,
  `level` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lookup_order_statuses` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `label_fa` varchar(50) NOT NULL,
  `label_en` varchar(50) NOT NULL,
  `color` varchar(7) DEFAULT '#6c757d',
  `is_final` tinyint(1) NOT NULL DEFAULT 0,
  `can_cancel` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lookup_payment_methods` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `label_fa` varchar(50) NOT NULL,
  `label_en` varchar(50) NOT NULL,
  `requires_gateway` tinyint(1) NOT NULL DEFAULT 0,
  `requires_verification` tinyint(1) NOT NULL DEFAULT 0,
  `is_online` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lookup_warranty_statuses` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `label_fa` varchar(50) NOT NULL,
  `label_en` varchar(50) NOT NULL,
  `color` varchar(7) DEFAULT '#6c757d',
  `is_final` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` tinyint(3) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `filename` varchar(255) NOT NULL,
  `executed_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `migration_exceptions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `source_table` varchar(64) NOT NULL,
  `source_id` int(10) UNSIGNED NOT NULL,
  `reason_code` varchar(64) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)),
  `status` enum('open','resolved','accepted_loss') NOT NULL DEFAULT 'open',
  `resolved_by` int(10) UNSIGNED DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `migration_gate_results` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `migration_version` varchar(20) NOT NULL,
  `gate_name` varchar(100) NOT NULL,
  `gate_status` enum('PASS','FAIL','BLOCKED') NOT NULL,
  `result_count` bigint(20) DEFAULT NULL,
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
  `checked_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `new_amp_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `voucher_id` varchar(36) NOT NULL,
  `account_code` varchar(50) NOT NULL COMMENT 'e.g., inventory_new, cogs',
  `debit` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Amperes',
  `credit` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Amperes',
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(10) UNSIGNED NOT NULL,
  `description` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `notifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED DEFAULT NULL,
  `type` varchar(50) NOT NULL COMMENT 'e.g., order_paid, promotion',
  `title` varchar(200) NOT NULL,
  `body` text NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Additional notification data' CHECK (json_valid(`data`)),
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_number` varchar(20) NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `order_type` enum('ONLINE','INSTORE','wholesale','pos','ampere_credit') NOT NULL DEFAULT 'ONLINE',
  `status` enum('pending_payment','paid','processing','shipped','delivered','completed','cancelled','expired') NOT NULL DEFAULT 'pending_payment',
  `subtotal` bigint(20) UNSIGNED NOT NULL,
  `discount_amount` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `shipping_fee` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `installation_fee` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `used_battery_credit` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `total_amount` bigint(20) UNSIGNED NOT NULL,
  `shipping_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`shipping_address`)),
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `installation_date` date DEFAULT NULL,
  `installation_time_slot` enum('morning','noon','afternoon','evening','night') DEFAULT NULL,
  `customer_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `shipped_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL COMMENT 'Auto-cancel if not paid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `transaction_uuid` char(36) DEFAULT NULL
);

DELIMITER $$
CREATE TRIGGER `trg_orders_status_change` AFTER UPDATE ON `orders` FOR EACH ROW BEGIN
  IF OLD.status != NEW.status THEN
    INSERT INTO order_status_history (order_id, from_status, to_status, notes)
    VALUES (NEW.id, OLD.status, NEW.status, CONCAT('Auto-logged by trigger at ', NOW()));
  END IF;
END$$
DELIMITER ;

CREATE TABLE `order_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(10) UNSIGNED NOT NULL,
  `returned_quantity` int(11) NOT NULL DEFAULT 0,
  `unit_price` bigint(20) UNSIGNED NOT NULL,
  `total_price` bigint(20) UNSIGNED NOT NULL,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_amperage` int(10) UNSIGNED DEFAULT NULL,
  `used_battery_amperage_2` int(10) UNSIGNED DEFAULT NULL,
  `used_battery_rate` int(10) UNSIGNED DEFAULT NULL COMMENT 'Rate per amp at order time',
  `used_battery_credit` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `used_battery_received` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_received_at` datetime DEFAULT NULL,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `installation_fee` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `warranty_months` int(11) NOT NULL,
  `warranty_start_date` date DEFAULT NULL,
  `warranty_end_date` date DEFAULT NULL,
  `warranty_code` varchar(50) DEFAULT NULL,
  `product_snapshot` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`product_snapshot`)),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `pricing_source` enum('manual_override','dedicated','brand_rate','legacy') DEFAULT NULL,
  `pricing_rate_id` int(10) UNSIGNED DEFAULT NULL,
  `pricing_rate_version` int(10) UNSIGNED DEFAULT NULL,
  `pricing_rate_snapshot` bigint(20) UNSIGNED DEFAULT NULL,
  `pricing_amperage_snapshot` int(10) UNSIGNED DEFAULT NULL,
  `pricing_unit_rial` bigint(20) UNSIGNED DEFAULT NULL
);

CREATE TABLE `order_item_used_batteries` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_item_id` int(10) UNSIGNED NOT NULL,
  `slot_number` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `amperage` int(10) UNSIGNED NOT NULL,
  `rate_per_amp` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `credit_amount` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_status_history` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `from_status` varchar(20) DEFAULT NULL,
  `to_status` varchar(20) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'Admin user ID',
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_used_batteries` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `order_item_id` int(10) UNSIGNED NOT NULL,
  `declared_amperage` int(10) UNSIGNED NOT NULL COMMENT 'آمپر اعلام شده توسط مشتری',
  `actual_amperage` int(10) UNSIGNED DEFAULT NULL COMMENT 'آمپر واقعی هنگام دریافت',
  `used_rate` int(10) UNSIGNED NOT NULL COMMENT 'نرخ هر آمپر در زمان سفارش',
  `status` enum('pending','received_exact','received_less','received_more','rejected','paid_cash','cancelled_by_return') NOT NULL DEFAULT 'pending',
  `settlement_type` enum('none','customer_pays','shop_pays','cash_settlement') DEFAULT NULL,
  `settlement_amount` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'مبلغ تسویه',
  `received_at` datetime DEFAULT NULL,
  `received_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'Admin who received',
  `rejection_reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `otp_codes` (
  `id` int(10) UNSIGNED NOT NULL,
  `mobile` varchar(11) NOT NULL,
  `code` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `verified_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL,
  `purpose` varchar(30) NOT NULL DEFAULT 'login'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_amperage_ledger` (
  `id` int(11) NOT NULL,
  `partner_id` int(10) UNSIGNED NOT NULL,
  `type` enum('receive','delivery') NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `amperage` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_ampere_accounts` (
  `id` int(10) UNSIGNED NOT NULL,
  `partner_id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED NOT NULL,
  `balance_ampere` int(11) NOT NULL DEFAULT 0,
  `credit_limit_ampere` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `is_blocked` tinyint(1) NOT NULL DEFAULT 0,
  `blocked_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);

CREATE TABLE `partner_ampere_transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `partner_id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED NOT NULL,
  `transaction_type` enum('SALE','SETTLEMENT') NOT NULL,
  `delta_ampere` int(11) NOT NULL,
  `rate_per_ampere` decimal(14,4) DEFAULT NULL,
  `rial_amount` bigint(20) UNSIGNED DEFAULT NULL,
  `balance_after` int(11) NOT NULL,
  `voucher_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_checks` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `check_number` varchar(50) NOT NULL,
  `bank_name` varchar(50) NOT NULL,
  `amount` bigint(20) UNSIGNED NOT NULL,
  `due_date` date NOT NULL,
  `status` enum('pending','endorsed','bounced','cleared','returned') NOT NULL DEFAULT 'pending',
  `version` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `status_notes` text DEFAULT NULL,
  `processed_by` int(10) UNSIGNED DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `return_reason` varchar(500) DEFAULT NULL,
  `returned_at` datetime DEFAULT NULL,
  `returned_by` int(10) UNSIGNED DEFAULT NULL,
  `return_voucher_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_credits` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `credit_limit` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Maximum credit in Rial',
  `credit_used` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Currently used credit',
  `is_blocked` tinyint(1) NOT NULL DEFAULT 0,
  `blocked_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partner_transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `type` enum('credit_purchase','payment','adjustment') NOT NULL,
  `amount` bigint(20) NOT NULL COMMENT 'Positive = debit, Negative = credit',
  `balance_after` bigint(20) NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL COMMENT 'order, check, manual',
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `party_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `voucher_id` varchar(36) NOT NULL,
  `party_id` int(10) UNSIGNED NOT NULL COMMENT 'Customer/Supplier ID',
  `account_code` varchar(50) NOT NULL COMMENT 'e.g., payable, receivable',
  `debit` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Rial',
  `credit` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Rial',
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(10) UNSIGNED NOT NULL,
  `description` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `party_type` enum('supplier','customer') NOT NULL DEFAULT 'customer'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `method` enum('online','card_transfer','cod','cash','pos','credit','check','transfer') NOT NULL,
  `amount` bigint(20) UNSIGNED NOT NULL,
  `status` enum('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `gateway` varchar(50) DEFAULT NULL COMMENT 'e.g., zarinpal',
  `gateway_ref` varchar(100) DEFAULT NULL,
  `gateway_track_id` varchar(100) DEFAULT NULL,
  `transfer_receipt_image` varchar(255) DEFAULT NULL,
  `transfer_verified_at` datetime DEFAULT NULL,
  `transfer_verified_by` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `check_number` varchar(50) DEFAULT NULL COMMENT 'شماره چک',
  `check_date` date DEFAULT NULL COMMENT 'تاریخ چک',
  `check_bank` varchar(100) DEFAULT NULL COMMENT 'نام بانک',
  `check_account_holder` varchar(100) DEFAULT NULL COMMENT 'نام صاحب حساب',
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
);

CREATE TABLE `permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(50) NOT NULL COMMENT 'e.g., products.create, orders.view',
  `name` varchar(100) NOT NULL COMMENT 'Persian name',
  `group_name` varchar(50) NOT NULL COMMENT 'e.g., products, orders',
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `price_history` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `field_name` enum('price','original_price','cost_price') NOT NULL DEFAULT 'price',
  `old_value` bigint(20) UNSIGNED NOT NULL,
  `new_value` bigint(20) UNSIGNED NOT NULL,
  `change_percentage` decimal(6,2) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `changed_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED NOT NULL,
  `amperage_id` int(10) UNSIGNED DEFAULT NULL,
  `case_type_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `sku` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `polarity` enum('R','L','LR') DEFAULT 'R',
  `voltage` decimal(4,1) DEFAULT NULL COMMENT 'Battery voltage',
  `price` bigint(20) UNSIGNED NOT NULL COMMENT 'Price in Rial',
  `wholesale_price` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Wholesale selling price in Rial (???????? ???????? ????????)',
  `retail_price` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Retail selling price in Rial (???????? ???????? ????????)',
  `original_price` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Original price before discount',
  `cost_price` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Cost price for profit calculation',
  `stock_quantity` int(11) NOT NULL DEFAULT 0,
  `min_stock_alert` int(11) NOT NULL DEFAULT 5,
  `warranty_months` int(11) NOT NULL DEFAULT 12,
  `weight` decimal(8,2) DEFAULT NULL COMMENT 'Weight in kg',
  `dimensions` varchar(50) DEFAULT NULL COMMENT 'LxWxH in cm',
  `has_installation` tinyint(1) NOT NULL DEFAULT 1,
  `installation_fee` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `max_per_order` int(10) UNSIGNED DEFAULT NULL COMMENT 'Max quantity per order',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `view_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `cca` int(10) UNSIGNED DEFAULT NULL COMMENT 'Cold Cranking Amps - ?????????? ????????????????????? ??????',
  `rc` int(10) UNSIGNED DEFAULT NULL COMMENT 'Reserve Capacity - ?????????? ?????????? ???? ?????? ??????????'
);

CREATE TABLE `product_images` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `url` varchar(255) NOT NULL,
  `alt_text` varchar(200) DEFAULT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_specifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `spec_key` varchar(50) NOT NULL,
  `spec_value` varchar(200) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `provinces` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchases` (
  `id` int(10) UNSIGNED NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `supplier_name` varchar(100) DEFAULT NULL,
  `total_amount` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `date` date DEFAULT NULL,
  `voucher_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchase_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `purchase_id` int(10) UNSIGNED NOT NULL,
  `product_name` varchar(255) NOT NULL DEFAULT '',
  `brand` varchar(100) NOT NULL DEFAULT '',
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `amperage` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `returned_quantity` int(11) NOT NULL DEFAULT 0,
  `unit_price` bigint(20) UNSIGNED NOT NULL,
  `total_price` bigint(20) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL
);

CREATE TABLE `purchase_returns` (
  `id` int(10) UNSIGNED NOT NULL,
  `purchase_id` int(10) UNSIGNED NOT NULL,
  `return_no` varchar(20) NOT NULL COMMENT 'PRT-YYYYMMDD-XXXX',
  `total_refund` bigint(20) UNSIGNED NOT NULL,
  `reason` text DEFAULT NULL,
  `voucher_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchase_return_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `purchase_return_id` int(10) UNSIGNED NOT NULL,
  `purchase_item_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` bigint(20) UNSIGNED NOT NULL,
  `total_price` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rate_limits` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `identifier` varchar(100) NOT NULL,
  `identifier_type` enum('ip','mobile','user_id') NOT NULL,
  `action` varchar(50) NOT NULL,
  `window_start` datetime NOT NULL,
  `request_count` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `blocked_until` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `refresh_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type` varchar(20) NOT NULL DEFAULT 'customer',
  `jti` varchar(32) NOT NULL COMMENT 'JWT ID',
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `registration_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `mobile` varchar(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rial_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `voucher_id` varchar(36) NOT NULL COMMENT 'Unique transaction ID',
  `account_code` varchar(50) NOT NULL COMMENT 'e.g., bank, sales_revenue',
  `debit` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `credit` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `reference_type` varchar(50) NOT NULL COMMENT 'e.g., order, payment',
  `reference_id` int(10) UNSIGNED NOT NULL,
  `description` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `transaction_uuid` char(36) DEFAULT NULL,
  `line_no` smallint(5) UNSIGNED DEFAULT NULL,
  `party_id` int(10) UNSIGNED DEFAULT NULL,
  `party_type` enum('customer','partner','supplier','factory','internal') DEFAULT NULL,
  `account_code_legacy` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale_returns` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL COMMENT 'FK to original order',
  `return_no` varchar(30) NOT NULL COMMENT 'SRT-YYYYMMDD-XXXX',
  `customer_id` int(10) UNSIGNED NOT NULL,
  `total_refund` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `reason` text DEFAULT NULL,
  `battery_condition` enum('healthy','defective') NOT NULL DEFAULT 'healthy' COMMENT 'healthy=back to stock, defective=warranty/scrap',
  `refund_method` varchar(30) NOT NULL DEFAULT 'cash' COMMENT 'cash, pos, credit',
  `voucher_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale_return_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `sale_return_id` int(10) UNSIGNED NOT NULL,
  `order_item_id` int(10) UNSIGNED NOT NULL COMMENT 'FK to original order_items row',
  `product_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` bigint(20) UNSIGNED NOT NULL COMMENT 'price at time of original sale',
  `total_price` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `schema_migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `version` varchar(20) NOT NULL COMMENT 'e.g. 1.0.0',
  `phase` varchar(50) NOT NULL,
  `description` varchar(500) NOT NULL,
  `script_name` varchar(100) NOT NULL,
  `executed_by` varchar(50) NOT NULL DEFAULT 'system',
  `execution_time_ms` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('pending','running','completed','failed','rolled_back') NOT NULL DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sensitive_data_access_log` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('customer','admin') NOT NULL,
  `action` enum('view','export','decrypt') NOT NULL,
  `data_type` varchar(50) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` int(10) UNSIGNED NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `setting_group` varchar(50) NOT NULL COMMENT 'e.g., shop, payment, delivery',
  `setting_key` varchar(50) NOT NULL,
  `value` text NOT NULL,
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `shipments` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `shipment_number` varchar(30) NOT NULL,
  `carrier_name` varchar(50) NOT NULL,
  `tracking_code` varchar(50) DEFAULT NULL,
  `status` enum('preparing','ready_to_ship','picked_up','in_transit','out_for_delivery','delivered','returned','failed') NOT NULL DEFAULT 'preparing',
  `shipping_cost` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `shipped_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sms_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `mobile` varchar(11) NOT NULL,
  `template_code` varchar(50) NOT NULL,
  `message_body` text DEFAULT NULL,
  `params` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`params`)),
  `provider` varchar(30) NOT NULL DEFAULT 'kavenegar',
  `status` enum('queued','sending','sent','delivered','failed') NOT NULL DEFAULT 'queued',
  `error_message` varchar(500) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_audits` (
  `id` int(10) UNSIGNED NOT NULL,
  `status` enum('draft','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_audit_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `stock_audit_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `system_count` int(11) NOT NULL,
  `physical_count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_audit_sessions` (
  `id` int(10) UNSIGNED NOT NULL,
  `status` enum('in_progress','completed','cancelled') NOT NULL DEFAULT 'in_progress',
  `started_by` int(10) UNSIGNED DEFAULT NULL,
  `started_at` datetime NOT NULL,
  `closed_by` int(10) UNSIGNED DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_notifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `status` enum('waiting','notified','cancelled') NOT NULL DEFAULT 'waiting',
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `suppliers` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `uploaded_files` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `type` enum('receipt','product','profile','general') NOT NULL DEFAULT 'general',
  `filename` varchar(100) NOT NULL,
  `original_name` varchar(200) NOT NULL,
  `mime_type` varchar(50) NOT NULL,
  `size` int(10) UNSIGNED NOT NULL,
  `path` varchar(255) NOT NULL,
  `url` varchar(500) NOT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `used_amp_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `voucher_id` varchar(36) NOT NULL,
  `account_code` varchar(50) NOT NULL COMMENT 'e.g., liability, inventory_used, receivable',
  `debit` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Amperes',
  `credit` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Amperes',
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(10) UNSIGNED NOT NULL,
  `description` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `used_battery_rates` (
  `id` int(10) UNSIGNED NOT NULL,
  `rate_per_amp` int(10) UNSIGNED NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `is_current` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `v_ampere_balance` (
`battery_type` varchar(4)
,`account_code` varchar(50)
,`total_debit` decimal(32,0)
,`total_credit` decimal(32,0)
,`balance_amps` decimal(33,0)
);

CREATE TABLE `v_brand_ampere_rate_history` (
`id` int(10) unsigned
,`brand_id` int(10) unsigned
,`brand_name` varchar(50)
,`rate_type` enum('retail','wholesale','used_battery','ampere_credit')
,`product_id` int(10) unsigned
,`product_name` varchar(200)
,`amperage_id` int(10) unsigned
,`amperage_value` int(10) unsigned
,`rate_per_ampere` decimal(14,4)
,`effective_from` datetime
,`effective_to` datetime
,`is_current` tinyint(1)
,`version` int(10) unsigned
,`notes` varchar(500)
,`created_at` datetime
,`created_by` int(10) unsigned
,`creator_name` varchar(101)
);

CREATE TABLE `v_daily_sales_report` (
`sale_date` date
,`order_count` bigint(21)
,`total_revenue` decimal(42,0)
,`total_discount` decimal(42,0)
,`avg_order_value` decimal(24,4)
);

CREATE TABLE `v_inventory_summary` (
`product_id` int(10) unsigned
,`sku` varchar(50)
,`name` varchar(200)
,`category` varchar(50)
,`brand` varchar(50)
,`stock_quantity` int(11)
,`min_stock_alert` int(11)
,`price` bigint(20) unsigned
,`stock_status` varchar(12)
);

CREATE TABLE `v_partner_ampere_summary` (
`account_id` int(10) unsigned
,`partner_id` int(10) unsigned
,`partner_first_name` varchar(50)
,`partner_last_name` varchar(50)
,`partner_mobile` varchar(11)
,`brand_id` int(10) unsigned
,`brand_name` varchar(50)
,`balance_ampere` int(11)
,`credit_limit_ampere` int(10) unsigned
,`credit_remaining` bigint(11) unsigned
,`is_blocked` tinyint(1)
,`blocked_reason` varchar(255)
,`current_rate` decimal(14,4)
,`rate_effective_from` datetime
,`estimated_rial_value` bigint(20) unsigned
,`created_at` datetime
,`updated_at` datetime
);

CREATE TABLE `v_partner_ampere_transactions_detail` (
`transaction_id` int(10) unsigned
,`partner_id` int(10) unsigned
,`brand_id` int(10) unsigned
,`transaction_type` enum('SALE','SETTLEMENT')
,`delta_ampere` int(11)
,`rate_per_ampere` decimal(14,4)
,`rial_amount` bigint(20) unsigned
,`balance_after` int(11)
,`voucher_id` int(10) unsigned
,`description` varchar(500)
,`created_by` int(10) unsigned
,`created_at` datetime
,`brand_name` varchar(50)
,`partner_name` varchar(101)
);

CREATE TABLE `v_trial_balance` (
`account_code` varchar(50)
,`total_debit` decimal(42,0)
,`total_credit` decimal(42,0)
,`balance` decimal(43,0)
);

CREATE TABLE `v_warranty_dashboard` (
`status` enum('received','in_stock','sent_to_factory','factory_responded','approved','rejected','settled','closed')
,`action_type` enum('pending_factory','shop_replace','partner_replace')
,`total_count` bigint(21)
,`total_fees` decimal(42,0)
);

CREATE TABLE `warranties` (
  `id` int(10) UNSIGNED NOT NULL,
  `warranty_no` varchar(20) NOT NULL COMMENT 'WRN-YYYYMMDD-XXXX',
  `partner_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Partner who sold (NULL for end customer)',
  `order_item_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_first_name` varchar(100) NOT NULL COMMENT 'Bringer first name',
  `customer_last_name` varchar(100) NOT NULL,
  `customer_mobile` varchar(11) NOT NULL COMMENT 'Bringer mobile',
  `customer_national_code` varchar(10) DEFAULT NULL COMMENT 'Bringer national code',
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'Defective product',
  `factory_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Manufacturer (optional)',
  `serial_no` varchar(50) DEFAULT NULL COMMENT 'Defective battery serial',
  `defect_type_id` int(10) UNSIGNED NOT NULL COMMENT 'Expert defect classification',
  `defect_desc` text DEFAULT NULL COMMENT 'Detailed defect description by expert',
  `expiry_date` date NOT NULL COMMENT 'Warranty expiry date (priority field)',
  `warranty_fee` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Fee collected in Rial (not Toman)',
  `fee_payment` enum('cash','credit') NOT NULL DEFAULT 'cash' COMMENT 'How fee was paid',
  `action_type` enum('pending_factory','shop_replace','partner_replace') NOT NULL COMMENT 'Resolution status',
  `replace_product_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Replacement product',
  `replace_serial` varchar(50) DEFAULT NULL COMMENT 'Replacement battery serial',
  `status` enum('received','in_stock','sent_to_factory','factory_responded','approved','rejected','settled','closed') NOT NULL DEFAULT 'received',
  `factory_result` enum('approved','rejected') DEFAULT NULL,
  `factory_note` text DEFAULT NULL COMMENT 'Factory comments',
  `rejection_action` enum('return_to_customer','add_to_used','loss') DEFAULT NULL,
  `notes` text DEFAULT NULL COMMENT 'Admin notes',
  `created_by` int(10) UNSIGNED NOT NULL COMMENT 'Admin who registered',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `partner_settled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether partner settlement is done (1=settled, 0=not settled)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `warranty_actions` (
  `id` int(10) UNSIGNED NOT NULL,
  `warranty_claim_id` int(10) UNSIGNED DEFAULT NULL,
  `warranty_id` int(10) UNSIGNED DEFAULT NULL,
  `action_type` varchar(50) NOT NULL,
  `old_status` varchar(50) DEFAULT NULL,
  `new_status` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `warranty_claims` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_item_id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `description` text NOT NULL,
  `status` enum('pending','under_review','sent_to_factory','approved','rejected','settled','closed') NOT NULL DEFAULT 'pending',
  `partner_settled` tinyint(1) NOT NULL DEFAULT 0,
  `admin_response` text DEFAULT NULL,
  `reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `warranty_status_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `warranty_id` int(10) UNSIGNED NOT NULL,
  `old_status` enum('received','in_stock','sent_to_factory','factory_responded','settled','closed') DEFAULT NULL,
  `new_status` enum('received','in_stock','sent_to_factory','factory_responded','settled','closed') NOT NULL,
  `changed_by` int(10) UNSIGNED NOT NULL COMMENT 'Admin',
  `note` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `wishlists` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `_deprecated_used_battery_inventory` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `order_item_id` int(10) UNSIGNED NOT NULL,
  `declared_amperage` int(10) UNSIGNED NOT NULL,
  `actual_amperage` int(10) UNSIGNED DEFAULT NULL,
  `rate_per_amp` int(10) UNSIGNED NOT NULL,
  `status` enum('pending','received','rejected') NOT NULL DEFAULT 'pending',
  `settlement_type` enum('none','customer_pays','shop_pays') DEFAULT NULL,
  `settlement_amount` bigint(20) UNSIGNED DEFAULT NULL,
  `received_by` int(10) UNSIGNED DEFAULT NULL,
  `received_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `v_ampere_balance`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_ampere_balance`  AS SELECT 'new' AS `battery_type`, `new_amp_ledger`.`account_code` AS `account_code`, sum(`new_amp_ledger`.`debit`) AS `total_debit`, sum(`new_amp_ledger`.`credit`) AS `total_credit`, sum(`new_amp_ledger`.`debit`) - sum(`new_amp_ledger`.`credit`) AS `balance_amps` FROM `new_amp_ledger` GROUP BY `new_amp_ledger`.`account_code`union all select 'used' AS `used`,`used_amp_ledger`.`account_code` AS `account_code`,sum(`used_amp_ledger`.`debit`) AS `SUM(debit)`,sum(`used_amp_ledger`.`credit`) AS `SUM(credit)`,sum(`used_amp_ledger`.`debit`) - sum(`used_amp_ledger`.`credit`) AS `SUM(debit) - SUM(credit)` from `used_amp_ledger` group by `used_amp_ledger`.`account_code`;

DROP TABLE IF EXISTS `v_brand_ampere_rate_history`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_brand_ampere_rate_history`  AS SELECT `bar`.`id` AS `id`, `bar`.`brand_id` AS `brand_id`, `b`.`name` AS `brand_name`, `bar`.`rate_type` AS `rate_type`, `bar`.`product_id` AS `product_id`, `p`.`name` AS `product_name`, `bar`.`amperage_id` AS `amperage_id`, `a`.`amperage` AS `amperage_value`, `bar`.`rate_per_ampere` AS `rate_per_ampere`, `bar`.`effective_from` AS `effective_from`, `bar`.`effective_to` AS `effective_to`, `bar`.`is_current` AS `is_current`, `bar`.`version` AS `version`, `bar`.`notes` AS `notes`, `bar`.`created_at` AS `created_at`, `bar`.`created_by` AS `created_by`, concat(`au`.`first_name`,' ',`au`.`last_name`) AS `creator_name` FROM ((((`brand_ampere_rates` `bar` left join `brands` `b` on(`b`.`id` = `bar`.`brand_id`)) left join `products` `p` on(`p`.`id` = `bar`.`product_id`)) left join `amperages` `a` on(`a`.`id` = `bar`.`amperage_id`)) left join `admin_users` `au` on(`au`.`id` = `bar`.`created_by`)) ORDER BY `bar`.`brand_id` ASC, `bar`.`rate_type` ASC, `bar`.`effective_from` DESC;

DROP TABLE IF EXISTS `v_daily_sales_report`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_daily_sales_report`  AS SELECT cast(`o`.`created_at` as date) AS `sale_date`, count(distinct `o`.`id`) AS `order_count`, sum(`o`.`total_amount`) AS `total_revenue`, sum(`o`.`discount_amount`) AS `total_discount`, avg(`o`.`total_amount`) AS `avg_order_value` FROM `orders` AS `o` WHERE `o`.`status` in ('paid','processing','shipped','delivered','completed') GROUP BY cast(`o`.`created_at` as date) ORDER BY cast(`o`.`created_at` as date) DESC;

DROP TABLE IF EXISTS `v_inventory_summary`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_inventory_summary`  AS SELECT `p`.`id` AS `product_id`, `p`.`sku` AS `sku`, `p`.`name` AS `name`, `c`.`name` AS `category`, `b`.`name` AS `brand`, `p`.`stock_quantity` AS `stock_quantity`, `p`.`min_stock_alert` AS `min_stock_alert`, `p`.`price` AS `price`, CASE WHEN `p`.`stock_quantity` <= 0 THEN 'OUT_OF_STOCK' WHEN `p`.`stock_quantity` <= `p`.`min_stock_alert` THEN 'LOW_STOCK' ELSE 'IN_STOCK' END AS `stock_status` FROM ((`products` `p` left join `categories` `c` on(`c`.`id` = `p`.`category_id`)) left join `brands` `b` on(`b`.`id` = `p`.`brand_id`)) WHERE `p`.`is_active` = 1 AND `p`.`deleted_at` is null ORDER BY `p`.`stock_quantity` ASC;

DROP TABLE IF EXISTS `v_partner_ampere_summary`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_partner_ampere_summary`  AS SELECT `paa`.`id` AS `account_id`, `paa`.`partner_id` AS `partner_id`, `c`.`first_name` AS `partner_first_name`, `c`.`last_name` AS `partner_last_name`, `c`.`mobile` AS `partner_mobile`, `paa`.`brand_id` AS `brand_id`, `b`.`name` AS `brand_name`, `paa`.`balance_ampere` AS `balance_ampere`, `paa`.`credit_limit_ampere` AS `credit_limit_ampere`, `paa`.`credit_limit_ampere`- `paa`.`balance_ampere` AS `credit_remaining`, `paa`.`is_blocked` AS `is_blocked`, `paa`.`blocked_reason` AS `blocked_reason`, `bar`.`rate_per_ampere` AS `current_rate`, `bar`.`effective_from` AS `rate_effective_from`, cast(`paa`.`balance_ampere` * coalesce(`bar`.`rate_per_ampere`,0) as unsigned) AS `estimated_rial_value`, `paa`.`created_at` AS `created_at`, `paa`.`updated_at` AS `updated_at` FROM (((`partner_ampere_accounts` `paa` join `customers` `c` on(`c`.`id` = `paa`.`partner_id`)) join `brands` `b` on(`b`.`id` = `paa`.`brand_id`)) left join `brand_ampere_rates` `bar` on(`bar`.`brand_id` = `paa`.`brand_id` and `bar`.`rate_type` = 'ampere_credit' and `bar`.`is_current` = 1 and `bar`.`amperage_id` is null and `bar`.`product_id` is null));

DROP TABLE IF EXISTS `v_partner_ampere_transactions_detail`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_partner_ampere_transactions_detail`  AS SELECT `pat`.`id` AS `transaction_id`, `pat`.`partner_id` AS `partner_id`, `pat`.`brand_id` AS `brand_id`, `pat`.`transaction_type` AS `transaction_type`, `pat`.`delta_ampere` AS `delta_ampere`, `pat`.`rate_per_ampere` AS `rate_per_ampere`, `pat`.`rial_amount` AS `rial_amount`, `pat`.`balance_after` AS `balance_after`, `pat`.`voucher_id` AS `voucher_id`, `pat`.`description` AS `description`, `pat`.`created_by` AS `created_by`, `pat`.`created_at` AS `created_at`, `b`.`name` AS `brand_name`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `partner_name` FROM ((`partner_ampere_transactions` `pat` left join `brands` `b` on(`pat`.`brand_id` = `b`.`id`)) left join `customers` `c` on(`pat`.`partner_id` = `c`.`id`));

DROP TABLE IF EXISTS `v_trial_balance`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_trial_balance`  AS SELECT `rial_ledger`.`account_code` AS `account_code`, sum(`rial_ledger`.`debit`) AS `total_debit`, sum(`rial_ledger`.`credit`) AS `total_credit`, sum(`rial_ledger`.`debit`) - sum(`rial_ledger`.`credit`) AS `balance` FROM `rial_ledger` GROUP BY `rial_ledger`.`account_code` ORDER BY `rial_ledger`.`account_code` ASC;

DROP TABLE IF EXISTS `v_warranty_dashboard`;

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `v_warranty_dashboard`  AS SELECT `w`.`status` AS `status`, `w`.`action_type` AS `action_type`, count(0) AS `total_count`, sum(`w`.`warranty_fee`) AS `total_fees` FROM `warranties` AS `w` GROUP BY `w`.`status`, `w`.`action_type` ORDER BY `w`.`status` ASC;

ALTER TABLE `accounting_vouchers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_uuid` (`uuid`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_date` (`date`),
  ADD KEY `idx_voucher_tx` (`transaction_uuid`);

ALTER TABLE `admin_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_admin_permission` (`admin_id`,`permission_id`),
  ADD KEY `idx_admin` (`admin_id`),
  ADD KEY `idx_permission` (`permission_id`);

ALTER TABLE `admin_refresh_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_jti` (`jti`),
  ADD KEY `idx_admin_id` (`admin_id`),
  ADD KEY `idx_expires` (`expires_at`);

ALTER TABLE `admin_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_admin` (`admin_id`);

ALTER TABLE `admin_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_username` (`username`);

ALTER TABLE `amperages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_value` (`amperage`);

ALTER TABLE `amperage_standards`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_amperage` (`amperage`);

ALTER TABLE `ampere_accounts`
  ADD PRIMARY KEY (`code`);

ALTER TABLE `ampere_ledger`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_amp_tx_line` (`transaction_uuid`,`line_no`),
  ADD KEY `idx_amp_account` (`account_code`,`party_id`,`brand_id`,`created_at`),
  ADD KEY `fk_amp_rate` (`rate_id`);

ALTER TABLE `ampere_rial_reconciliation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_reconciliation_tx` (`transaction_uuid`),
  ADD KEY `idx_reconciliation_open` (`status`,`resolved_at`);

ALTER TABLE `api_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_admin` (`admin_id`);

ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`,`user_type`),
  ADD KEY `idx_entity` (`entity_type`,`entity_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_created` (`created_at`);

ALTER TABLE `battery_case_types`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_name` (`name`);

ALTER TABLE `battery_recommendation_rules`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_rule_code` (`rule_code`);

ALTER TABLE `brands`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_slug` (`slug`),
  ADD KEY `fk_brands_factory` (`factory_id`);

ALTER TABLE `brand_ampere_rates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_bar_brand_effective` (`brand_id`,`effective_from`),
  ADD KEY `idx_bar_brand_current` (`brand_id`,`is_current`),
  ADD KEY `fk_bar_created_by` (`created_by`),
  ADD KEY `idx_bar_lookup` (`rate_type`,`brand_id`,`amperage_id`,`product_id`,`is_current`),
  ADD KEY `idx_bar_history` (`brand_id`,`rate_type`,`effective_from`),
  ADD KEY `idx_bar_product` (`product_id`,`rate_type`,`is_current`),
  ADD KEY `idx_bar_current_type` (`brand_id`,`rate_type`,`is_current`,`effective_from`);

ALTER TABLE `business_transactions`
  ADD PRIMARY KEY (`uuid`),
  ADD UNIQUE KEY `uq_btx_no` (`tx_no`),
  ADD UNIQUE KEY `uq_btx_idempotency` (`idempotency_key`),
  ADD UNIQUE KEY `uq_btx_legacy` (`legacy_source`,`legacy_id`),
  ADD KEY `idx_btx_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_btx_status` (`status`,`created_at`),
  ADD KEY `fk_btx_period` (`fiscal_period_id`),
  ADD KEY `fk_btx_reversal` (`reverses_uuid`);

ALTER TABLE `carts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_customer` (`customer_id`);

ALTER TABLE `cart_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cart` (`cart_id`),
  ADD KEY `idx_product` (`product_id`);

ALTER TABLE `cart_item_used_batteries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_item_slot` (`cart_item_id`,`slot_number`),
  ADD KEY `idx_cart_item` (`cart_item_id`);

ALTER TABLE `car_brands`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_slug` (`slug`);

ALTER TABLE `car_compatibility`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_model` (`model_id`);

ALTER TABLE `car_models`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_brand_slug` (`brand_id`,`slug`),
  ADD KEY `idx_brand` (`brand_id`);

ALTER TABLE `case_type_amperage_range`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_case_type` (`case_type_id`);

ALTER TABLE `case_type_compatibility`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_source_target` (`source_case_type_id`,`target_case_type_id`),
  ADD KEY `idx_source` (`source_case_type_id`),
  ADD KEY `fk_compat_target` (`target_case_type_id`);

ALTER TABLE `case_type_upgrades`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_case_amp` (`case_type_id`,`supports_amperage_id`),
  ADD KEY `fk_ctu_amperage` (`supports_amperage_id`);

ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_slug` (`slug`);

ALTER TABLE `chart_of_accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`),
  ADD UNIQUE KEY `uq_chart_account_code` (`code`),
  ADD KEY `idx_type` (`account_type`);

ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_province` (`province_id`);

ALTER TABLE `coupons`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`),
  ADD KEY `idx_active` (`is_active`),
  ADD KEY `idx_expires` (`expires_at`),
  ADD KEY `idx_active_dates` (`is_active`,`starts_at`,`expires_at`);

ALTER TABLE `coupon_usages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_coupon` (`coupon_id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `fk_usage_order` (`order_id`);

ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_mobile` (`mobile`),
  ADD UNIQUE KEY `uk_national_code` (`national_code`),
  ADD UNIQUE KEY `uk_employee_code` (`employee_code`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_user_type` (`user_type`),
  ADD KEY `idx_user_type_employee` (`user_type`,`employee_code`);

ALTER TABLE `customer_addresses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `fk_addr_province` (`province_id`),
  ADD KEY `fk_addr_city` (`city_id`);

ALTER TABLE `customer_grade_tiers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_name` (`name`),
  ADD KEY `idx_min_amperage` (`min_amperage`);

ALTER TABLE `customer_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer_id` (`customer_id`,`created_at`),
  ADD KEY `idx_admin_id` (`admin_id`);

ALTER TABLE `customer_reminders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_assigned_status_time` (`assigned_to_admin_id`,`status`,`remind_at`),
  ADD KEY `idx_customer_id` (`customer_id`),
  ADD KEY `fk_customer_reminders_creator` (`created_by_admin_id`);

ALTER TABLE `defect_types`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`);

ALTER TABLE `factories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `is_active` (`is_active`);

ALTER TABLE `financial_baseline`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_baseline_table` (`table_name`,`captured_at`);

ALTER TABLE `fiscal_periods`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`);

ALTER TABLE `idempotency_records`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_idempotency_key` (`idempotency_key`),
  ADD KEY `idx_ir_expires` (`expires_at`);

ALTER TABLE `image_processing_jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status_created` (`status`,`created_at`),
  ADD KEY `idx_uploaded_file` (`uploaded_file_id`);

ALTER TABLE `inventory_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_created` (`created_at`),
  ADD KEY `fk_inv_logs_created_by` (`created_by`),
  ADD KEY `idx_reference` (`reference_type`,`reference_id`);

ALTER TABLE `inventory_used`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `amperage` (`amperage`);

ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mobile_created` (`mobile`,`created_at`),
  ADD KEY `idx_ip_created` (`ip_address`,`created_at`);

ALTER TABLE `lookup_admin_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`);

ALTER TABLE `lookup_order_statuses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`);

ALTER TABLE `lookup_payment_methods`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`);

ALTER TABLE `lookup_warranty_statuses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`);

ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `filename` (`filename`);

ALTER TABLE `migration_exceptions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_migration_exception` (`source_table`,`source_id`);

ALTER TABLE `migration_gate_results`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_gate_version` (`migration_version`,`gate_status`);

ALTER TABLE `new_amp_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_voucher` (`voucher_id`),
  ADD KEY `idx_account` (`account_code`),
  ADD KEY `idx_reference` (`reference_type`,`reference_id`);

ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_read` (`is_read`),
  ADD KEY `idx_created` (`created_at`),
  ADD KEY `idx_admin_read` (`admin_id`,`is_read`);

ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_order_number` (`order_number`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created` (`created_at`),
  ADD KEY `idx_expires` (`expires_at`),
  ADD KEY `idx_customer_status_created` (`customer_id`,`status`,`created_at`),
  ADD KEY `idx_order_type_status` (`order_type`,`status`),
  ADD KEY `idx_pending_expires` (`status`,`expires_at`),
  ADD KEY `idx_orders_customer_id` (`customer_id`,`created_at`),
  ADD KEY `idx_orders_transaction_uuid` (`transaction_uuid`);

ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_warranty` (`warranty_code`),
  ADD KEY `idx_order_items_order_id` (`order_id`);

ALTER TABLE `order_item_used_batteries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_item_slot` (`order_item_id`,`slot_number`),
  ADD KEY `idx_order_item` (`order_item_id`);

ALTER TABLE `order_status_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `fk_osh_created_by` (`created_by`);

ALTER TABLE `order_used_batteries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_order_item` (`order_item_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `fk_oub_received_by` (`received_by`);

ALTER TABLE `otp_codes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mobile_created` (`mobile`,`created_at`),
  ADD KEY `idx_expires` (`expires_at`),
  ADD KEY `idx_otp_codes_mobile_created` (`mobile`,`created_at`);

ALTER TABLE `partner_amperage_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `partner_id` (`partner_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `idx_partner_amperage_ledger_partner_id` (`partner_id`,`created_at`);

ALTER TABLE `partner_ampere_accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_partner_brand` (`partner_id`,`brand_id`),
  ADD KEY `idx_paa_partner` (`partner_id`),
  ADD KEY `idx_paa_brand` (`brand_id`);

ALTER TABLE `partner_ampere_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_pat_partner_type_created` (`partner_id`,`transaction_type`,`created_at`),
  ADD KEY `idx_pat_voucher` (`voucher_id`),
  ADD KEY `idx_pat_type` (`transaction_type`),
  ADD KEY `idx_pat_brand` (`brand_id`),
  ADD KEY `fk_pat_created_by` (`created_by`);

ALTER TABLE `partner_checks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_due_date` (`due_date`),
  ADD KEY `idx_check_bank` (`check_number`,`bank_name`),
  ADD KEY `idx_partner_checks_returned_at` (`returned_at`),
  ADD KEY `idx_partner_checks_return_voucher` (`return_voucher_id`);

ALTER TABLE `partner_credits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_customer` (`customer_id`);

ALTER TABLE `partner_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_created` (`created_at`),
  ADD KEY `fk_partner_trans_created_by` (`created_by`);

ALTER TABLE `party_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_voucher` (`voucher_id`),
  ADD KEY `idx_party` (`party_id`),
  ADD KEY `idx_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_party_account` (`party_id`,`account_code`),
  ADD KEY `idx_party_ledger_party_type_id` (`party_type`,`party_id`),
  ADD KEY `idx_party_ledger_party_id` (`party_id`,`created_at`);

ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_gateway_ref` (`gateway_ref`),
  ADD KEY `idx_check_number` (`check_number`),
  ADD KEY `fk_payments_verified_by` (`transfer_verified_by`),
  ADD KEY `idx_method_status` (`method`,`status`),
  ADD KEY `idx_order_status` (`order_id`,`status`);

ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_code` (`code`),
  ADD KEY `idx_group` (`group_name`);

ALTER TABLE `price_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_created` (`product_id`,`created_at`);

ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_slug` (`slug`),
  ADD UNIQUE KEY `uk_sku` (`sku`),
  ADD KEY `idx_category` (`category_id`),
  ADD KEY `idx_brand` (`brand_id`),
  ADD KEY `idx_active` (`is_active`),
  ADD KEY `idx_price` (`price`),
  ADD KEY `fk_products_amperage` (`amperage_id`),
  ADD KEY `fk_products_case_type` (`case_type_id`),
  ADD KEY `idx_catalog_search` (`is_active`,`deleted_at`,`category_id`,`price`),
  ADD KEY `idx_brand_amperage` (`brand_id`,`is_active`);

ALTER TABLE `products` ADD FULLTEXT KEY `ft_search` (`name`,`description`,`short_description`);

ALTER TABLE `product_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product` (`product_id`);

ALTER TABLE `product_specifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product` (`product_id`);

ALTER TABLE `provinces`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_slug` (`slug`);

ALTER TABLE `purchases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `invoice_number` (`invoice_number`),
  ADD KEY `date` (`date`),
  ADD KEY `fk_purchases_supplier` (`supplier_id`);

ALTER TABLE `purchase_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `purchase_id` (`purchase_id`),
  ADD KEY `product_id` (`product_id`);

ALTER TABLE `purchase_returns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_return_no` (`return_no`),
  ADD KEY `fk_purchase_returns_purchase` (`purchase_id`),
  ADD KEY `fk_pret_created_by` (`created_by`);

ALTER TABLE `purchase_return_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_pri_return` (`purchase_return_id`),
  ADD KEY `fk_pri_item` (`purchase_item_id`);

ALTER TABLE `rate_limits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_action_window` (`action`,`window_start`);

ALTER TABLE `refresh_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_jti` (`jti`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_expires` (`expires_at`);

ALTER TABLE `registration_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_token` (`token`),
  ADD KEY `idx_mobile` (`mobile`),
  ADD KEY `idx_expires` (`expires_at`);

ALTER TABLE `rial_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_voucher` (`voucher_id`),
  ADD KEY `idx_account` (`account_code`),
  ADD KEY `idx_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_created` (`created_at`),
  ADD KEY `idx_account_created` (`account_code`,`created_at`),
  ADD KEY `idx_rial_tx` (`transaction_uuid`),
  ADD KEY `idx_rial_ref` (`reference_type`,`reference_id`);

ALTER TABLE `sale_returns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_return_no` (`return_no`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `fk_sret_created_by` (`created_by`);

ALTER TABLE `sale_return_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_sri_return` (`sale_return_id`),
  ADD KEY `fk_sri_order_item` (`order_item_id`),
  ADD KEY `fk_sri_product` (`product_id`);

ALTER TABLE `schema_migrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_version` (`version`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_phase` (`phase`);

ALTER TABLE `sensitive_data_access_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`,`user_type`),
  ADD KEY `idx_created` (`created_at`);

ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_group_key` (`setting_group`,`setting_key`);

ALTER TABLE `shipments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_shipment_number` (`shipment_number`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_status` (`status`);

ALTER TABLE `sms_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mobile` (`mobile`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created` (`created_at`);

ALTER TABLE `stock_audits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_stock_audits_status` (`status`);

ALTER TABLE `stock_audit_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_items_product_id` (`product_id`),
  ADD KEY `idx_audit_items_audit_id` (`stock_audit_id`);

ALTER TABLE `stock_audit_sessions`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `stock_notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_status` (`product_id`,`status`);

ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_supplier_name` (`name`),
  ADD KEY `idx_active` (`is_active`);

ALTER TABLE `uploaded_files`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_type` (`type`);

ALTER TABLE `used_amp_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_voucher` (`voucher_id`),
  ADD KEY `idx_account` (`account_code`),
  ADD KEY `idx_reference` (`reference_type`,`reference_id`);

ALTER TABLE `used_battery_rates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_current` (`is_current`);

ALTER TABLE `warranties`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_warranty_no` (`warranty_no`),
  ADD KEY `idx_partner` (`partner_id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_expiry` (`expiry_date`),
  ADD KEY `idx_action_type` (`action_type`),
  ADD KEY `idx_customer_mobile` (`customer_mobile`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `fk_warranties_defect` (`defect_type_id`),
  ADD KEY `fk_warranties_replace_product` (`replace_product_id`),
  ADD KEY `fk_warranties_created_by` (`created_by`),
  ADD KEY `fk_warranties_order_item` (`order_item_id`),
  ADD KEY `idx_factory_status` (`factory_id`,`status`),
  ADD KEY `idx_status_action` (`status`,`action_type`),
  ADD KEY `idx_customer` (`customer_id`,`created_at`);

ALTER TABLE `warranty_actions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `warranty_claim_id` (`warranty_claim_id`),
  ADD KEY `warranty_id` (`warranty_id`),
  ADD KEY `fk_wa_performed_by` (`performed_by`);

ALTER TABLE `warranty_claims`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_item` (`order_item_id`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `fk_wc_reviewed_by` (`reviewed_by`);

ALTER TABLE `warranty_status_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_warranty` (`warranty_id`),
  ADD KEY `fk_wsl_changed_by` (`changed_by`);

ALTER TABLE `wishlists`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_customer_product` (`customer_id`,`product_id`),
  ADD KEY `fk_wishlist_product` (`product_id`);

ALTER TABLE `_deprecated_used_battery_inventory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `fk_used_battery_item` (`order_item_id`);

ALTER TABLE `accounting_vouchers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `admin_permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `admin_refresh_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `admin_users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

ALTER TABLE `amperages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

ALTER TABLE `amperage_standards`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

ALTER TABLE `ampere_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ampere_rial_reconciliation`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `api_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `audit_logs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `battery_case_types`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

ALTER TABLE `battery_recommendation_rules`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `brands`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

ALTER TABLE `brand_ampere_rates`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `carts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cart_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cart_item_used_batteries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `car_brands`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=95;

ALTER TABLE `car_compatibility`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=863;

ALTER TABLE `car_models`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=407;

ALTER TABLE `case_type_amperage_range`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=45;

ALTER TABLE `case_type_compatibility`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

ALTER TABLE `case_type_upgrades`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

ALTER TABLE `chart_of_accounts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

ALTER TABLE `cities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=430;

ALTER TABLE `coupons`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `coupon_usages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `customers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `customer_addresses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `customer_grade_tiers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `customer_notes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `customer_reminders`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `defect_types`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

ALTER TABLE `factories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `financial_baseline`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `fiscal_periods`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `idempotency_records`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `image_processing_jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `inventory_logs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

ALTER TABLE `inventory_used`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `login_attempts`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `lookup_admin_roles`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `lookup_order_statuses`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

ALTER TABLE `lookup_payment_methods`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

ALTER TABLE `lookup_warranty_statuses`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `migration_exceptions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `migration_gate_results`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `new_amp_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `notifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `order_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `order_item_used_batteries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `order_status_history`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `order_used_batteries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `otp_codes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `partner_amperage_ledger`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

ALTER TABLE `partner_ampere_accounts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `partner_ampere_transactions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `partner_checks`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `partner_credits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `partner_transactions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `party_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

ALTER TABLE `payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

ALTER TABLE `price_history`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `products`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `product_images`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `product_specifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `provinces`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32;

ALTER TABLE `purchases`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `purchase_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `purchase_returns`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `purchase_return_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `rate_limits`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `refresh_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `registration_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `rial_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `sale_returns`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `sale_return_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `schema_migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

ALTER TABLE `sensitive_data_access_log`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `settings`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `shipments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `sms_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `stock_audits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `stock_audit_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `stock_audit_sessions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `stock_notifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `suppliers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `uploaded_files`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `used_amp_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `used_battery_rates`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `warranties`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `warranty_actions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `warranty_claims`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `warranty_status_logs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `wishlists`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `_deprecated_used_battery_inventory`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `admin_permissions`
  ADD CONSTRAINT `fk_admin_perm_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_admin_perm_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE;

ALTER TABLE `admin_refresh_tokens`
  ADD CONSTRAINT `fk_admin_refresh_tokens_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `admin_sessions`
  ADD CONSTRAINT `fk_admin_sessions_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `ampere_ledger`
  ADD CONSTRAINT `fk_amp_account` FOREIGN KEY (`account_code`) REFERENCES `ampere_accounts` (`code`),
  ADD CONSTRAINT `fk_amp_rate` FOREIGN KEY (`rate_id`) REFERENCES `brand_ampere_rates` (`id`),
  ADD CONSTRAINT `fk_amp_tx` FOREIGN KEY (`transaction_uuid`) REFERENCES `business_transactions` (`uuid`);

ALTER TABLE `api_tokens`
  ADD CONSTRAINT `fk_api_tokens_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `brands`
  ADD CONSTRAINT `fk_brands_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE SET NULL;

ALTER TABLE `brand_ampere_rates`
  ADD CONSTRAINT `fk_bar_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_bar_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_bar_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `business_transactions`
  ADD CONSTRAINT `fk_btx_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_btx_reversal` FOREIGN KEY (`reverses_uuid`) REFERENCES `business_transactions` (`uuid`);

ALTER TABLE `carts`
  ADD CONSTRAINT `fk_carts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `cart_items`
  ADD CONSTRAINT `fk_cart_items_cart` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_cart_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

ALTER TABLE `cart_item_used_batteries`
  ADD CONSTRAINT `fk_ciub_cart_item` FOREIGN KEY (`cart_item_id`) REFERENCES `cart_items` (`id`) ON DELETE CASCADE;

ALTER TABLE `car_compatibility`
  ADD CONSTRAINT `fk_cc_model_id` FOREIGN KEY (`model_id`) REFERENCES `car_models` (`id`) ON DELETE CASCADE;

ALTER TABLE `car_models`
  ADD CONSTRAINT `fk_cm_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `car_brands` (`id`) ON DELETE CASCADE;

ALTER TABLE `case_type_amperage_range`
  ADD CONSTRAINT `fk_range_case_type` FOREIGN KEY (`case_type_id`) REFERENCES `battery_case_types` (`id`) ON DELETE CASCADE;

ALTER TABLE `case_type_compatibility`
  ADD CONSTRAINT `fk_compat_source` FOREIGN KEY (`source_case_type_id`) REFERENCES `battery_case_types` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_compat_target` FOREIGN KEY (`target_case_type_id`) REFERENCES `battery_case_types` (`id`) ON DELETE CASCADE;

ALTER TABLE `case_type_upgrades`
  ADD CONSTRAINT `fk_ctu_amperage` FOREIGN KEY (`supports_amperage_id`) REFERENCES `amperages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ctu_case_type` FOREIGN KEY (`case_type_id`) REFERENCES `battery_case_types` (`id`) ON DELETE CASCADE;

ALTER TABLE `cities`
  ADD CONSTRAINT `fk_cities_province` FOREIGN KEY (`province_id`) REFERENCES `provinces` (`id`);

ALTER TABLE `coupon_usages`
  ADD CONSTRAINT `fk_usage_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`),
  ADD CONSTRAINT `fk_usage_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_usage_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

ALTER TABLE `customer_addresses`
  ADD CONSTRAINT `fk_addr_city` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_addr_province` FOREIGN KEY (`province_id`) REFERENCES `provinces` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_addresses_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `customer_notes`
  ADD CONSTRAINT `fk_customer_notes_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_customer_notes_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `customer_reminders`
  ADD CONSTRAINT `fk_customer_reminders_assignee` FOREIGN KEY (`assigned_to_admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_customer_reminders_creator` FOREIGN KEY (`created_by_admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_customer_reminders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `inventory_logs`
  ADD CONSTRAINT `fk_inv_logs_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_inventory_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

ALTER TABLE `notifications`
  ADD CONSTRAINT `fk_notifications_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_notifications_customer` FOREIGN KEY (`user_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

ALTER TABLE `order_items`
  ADD CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_order_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

ALTER TABLE `order_item_used_batteries`
  ADD CONSTRAINT `fk_oiub_order_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE;

ALTER TABLE `order_status_history`
  ADD CONSTRAINT `fk_osh_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_status_history_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE;

ALTER TABLE `order_used_batteries`
  ADD CONSTRAINT `fk_order_used_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`),
  ADD CONSTRAINT `fk_order_used_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  ADD CONSTRAINT `fk_oub_received_by` FOREIGN KEY (`received_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `partner_amperage_ledger`
  ADD CONSTRAINT `partner_amperage_ledger_ibfk_1` FOREIGN KEY (`partner_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `partner_amperage_ledger_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

ALTER TABLE `partner_ampere_accounts`
  ADD CONSTRAINT `fk_paa_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_paa_partner` FOREIGN KEY (`partner_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE;

ALTER TABLE `partner_ampere_transactions`
  ADD CONSTRAINT `fk_pat_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_pat_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_pat_partner` FOREIGN KEY (`partner_id`) REFERENCES `customers` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_pat_voucher` FOREIGN KEY (`voucher_id`) REFERENCES `accounting_vouchers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `partner_checks`
  ADD CONSTRAINT `fk_partner_checks_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `partner_credits`
  ADD CONSTRAINT `fk_partner_credits_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

ALTER TABLE `partner_transactions`
  ADD CONSTRAINT `fk_partner_trans_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_partner_trans_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

ALTER TABLE `payments`
  ADD CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  ADD CONSTRAINT `fk_payments_verified_by` FOREIGN KEY (`transfer_verified_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `price_history`
  ADD CONSTRAINT `fk_ph_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `products`
  ADD CONSTRAINT `fk_products_amperage` FOREIGN KEY (`amperage_id`) REFERENCES `amperages` (`id`),
  ADD CONSTRAINT `fk_products_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  ADD CONSTRAINT `fk_products_case_type` FOREIGN KEY (`case_type_id`) REFERENCES `battery_case_types` (`id`),
  ADD CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

ALTER TABLE `product_images`
  ADD CONSTRAINT `fk_images_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `product_specifications`
  ADD CONSTRAINT `fk_specs_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `purchases`
  ADD CONSTRAINT `fk_purchases_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE `purchase_returns`
  ADD CONSTRAINT `fk_pret_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_purchase_returns_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE;

ALTER TABLE `purchase_return_items`
  ADD CONSTRAINT `fk_pri_item` FOREIGN KEY (`purchase_item_id`) REFERENCES `purchase_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_pri_return` FOREIGN KEY (`purchase_return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE;

ALTER TABLE `sale_returns`
  ADD CONSTRAINT `fk_sale_returns_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sret_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sret_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE `sale_return_items`
  ADD CONSTRAINT `fk_sri_order_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sri_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sri_return` FOREIGN KEY (`sale_return_id`) REFERENCES `sale_returns` (`id`) ON DELETE CASCADE;

ALTER TABLE `shipments`
  ADD CONSTRAINT `fk_shipments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

ALTER TABLE `stock_audit_items`
  ADD CONSTRAINT `fk_audit_items_audit` FOREIGN KEY (`stock_audit_id`) REFERENCES `stock_audits` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `stock_notifications`
  ADD CONSTRAINT `fk_sn_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `warranties`
  ADD CONSTRAINT `fk_warranties_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_warranties_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_warranties_defect` FOREIGN KEY (`defect_type_id`) REFERENCES `defect_types` (`id`),
  ADD CONSTRAINT `fk_warranties_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_warranties_order_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_warranties_partner` FOREIGN KEY (`partner_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_warranties_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `fk_warranties_replace_product` FOREIGN KEY (`replace_product_id`) REFERENCES `products` (`id`);

ALTER TABLE `warranty_actions`
  ADD CONSTRAINT `fk_wa_performed_by` FOREIGN KEY (`performed_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_wa_warranty` FOREIGN KEY (`warranty_id`) REFERENCES `warranties` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_wa_warranty_claim` FOREIGN KEY (`warranty_claim_id`) REFERENCES `warranty_claims` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `warranty_claims`
  ADD CONSTRAINT `fk_warranty_claims_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_warranty_claims_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`),
  ADD CONSTRAINT `fk_wc_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `warranty_status_logs`
  ADD CONSTRAINT `fk_warranty_log_warranty` FOREIGN KEY (`warranty_id`) REFERENCES `warranties` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_wsl_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE `wishlists`
  ADD CONSTRAINT `fk_wishlist_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_wishlist_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `_deprecated_used_battery_inventory`
  ADD CONSTRAINT `fk_used_battery_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`),
  ADD CONSTRAINT `fk_used_battery_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);

DELIMITER $$
CREATE  EVENT `evt_cleanup_expired_otps` ON SCHEDULE EVERY 1 HOUR STARTS '2026-02-20 19:53:36' ON COMPLETION PRESERVE ENABLE COMMENT 'Remove expired OTP codes older than 24 hours' DO BEGIN
  DELETE FROM otp_codes WHERE expires_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);
END$$
DELIMITER ;

DELIMITER $$
CREATE  EVENT `evt_cleanup_expired_tokens` ON SCHEDULE EVERY 1 DAY STARTS '2026-02-20 20:53:36' ON COMPLETION PRESERVE ENABLE COMMENT 'Remove expired tokens and clean rate limits' DO BEGIN
  DELETE FROM refresh_tokens
  WHERE expires_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
    OR (revoked_at IS NOT NULL AND revoked_at < DATE_SUB(NOW(), INTERVAL 7 DAY));
  DELETE FROM registration_tokens WHERE expires_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
  DELETE FROM rate_limits WHERE window_start < DATE_SUB(NOW(), INTERVAL 7 DAY);
END$$
DELIMITER ;

DELIMITER $$
CREATE  EVENT `evt_cancel_expired_orders` ON SCHEDULE EVERY 5 MINUTE STARTS '2026-02-20 19:53:36' ON COMPLETION PRESERVE ENABLE COMMENT 'Auto-cancel orders past their expiry time' DO BEGIN
  UPDATE orders
  SET status = 'expired', cancelled_at = NOW(), updated_at = NOW()
  WHERE status = 'pending_payment'
    AND expires_at IS NOT NULL AND expires_at < NOW();

  INSERT INTO order_status_history (order_id, from_status, to_status, notes)
  SELECT id, 'pending_payment', 'expired', 'Auto-expired by system event'
  FROM orders
  WHERE status = 'expired' AND cancelled_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE);
END$$
DELIMITER ;

DELIMITER $$
CREATE  EVENT `evt_cleanup_idempotency` ON SCHEDULE EVERY 1 DAY STARTS '2026-02-22 20:24:58' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DELETE FROM `idempotency_records`
    WHERE `expires_at` < NOW();
END$$
DELIMITER ;

CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(64) NOT NULL DEFAULT 'default',
  `type` varchar(128) NOT NULL,
  `payload` longtext NOT NULL,
  `status` enum('pending','reserved','completed','failed') NOT NULL DEFAULT 'pending',
  `attempts` int(10) unsigned NOT NULL DEFAULT 0,
  `max_attempts` int(10) unsigned NOT NULL DEFAULT 3,
  `idempotency_key` varchar(128) DEFAULT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `reserved_by` varchar(128) DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  `updated_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_queue_idempotency` (`queue`,`idempotency_key`),
  KEY `idx_queue_status_available` (`queue`,`status`,`available_at`),
  KEY `idx_reserved_at` (`reserved_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `dead_letter_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `original_job_id` varchar(64) NOT NULL,
  `queue` varchar(64) NOT NULL DEFAULT 'default',
  `type` varchar(128) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` int(10) unsigned NOT NULL,
  `error_message` text DEFAULT NULL,
  `failed_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dead_letter_orig` (`original_job_id`),
  KEY `idx_queue_failed_at` (`queue`,`failed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

