CREATE TABLE IF NOT EXISTS `warranties` (
  `id`                    int(10) unsigned NOT NULL AUTO_INCREMENT,
  `warranty_no`           varchar(20)      NOT NULL COMMENT 'WAR-XXXXXX',
  `partner_id`            int(10) unsigned DEFAULT NULL,
  `order_item_id`         int(10) unsigned DEFAULT NULL,
  `customer_id`           int(10) unsigned DEFAULT NULL,
  `customer_first_name`   varchar(100)     NOT NULL,
  `customer_last_name`    varchar(100)     NOT NULL,
  `customer_mobile`       varchar(11)      NOT NULL,
  `customer_national_code` varchar(10)     DEFAULT NULL,
  `product_id`            int(10) unsigned NOT NULL,
  `factory_id`            int(10) unsigned DEFAULT NULL,
  `serial_no`             varchar(50)      DEFAULT NULL,
  `defect_type_id`        int(10) unsigned NOT NULL,
  `defect_desc`           text             DEFAULT NULL,
  `expiry_date`           date             NOT NULL,
  `warranty_fee`          bigint(20) unsigned NOT NULL DEFAULT 0,
  `fee_payment`           enum('cash','credit') NOT NULL DEFAULT 'cash',
  `action_type`           enum('pending_factory','shop_replace','partner_replace') NOT NULL DEFAULT 'pending_factory',
  `replace_product_id`    int(10) unsigned DEFAULT NULL,
  `replace_serial`        varchar(50)      DEFAULT NULL,
  `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,
  `rejection_action`      enum('return_to_customer','add_to_used','loss') DEFAULT NULL,
  `notes`                 text             DEFAULT NULL,
  `created_by`            int(10) unsigned NOT NULL,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_warranty_no` (`warranty_no`),
  KEY `idx_partner`  (`partner_id`),
  KEY `idx_product`  (`product_id`),
  KEY `idx_status`   (`status`),
  KEY `idx_created`  (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
