-- Courier Partner Setup Table
CREATE TABLE IF NOT EXISTS `tbl_courier_partner` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `partner_name` VARCHAR(100) NOT NULL COMMENT 'e.g., Delhivery B2B, Delhivery B2C, Ekart, Movin',
    `partner_code` VARCHAR(50) NOT NULL UNIQUE COMMENT 'Unique code for partner',
    `api_url` VARCHAR(255) DEFAULT NULL COMMENT 'API endpoint URL',
    `api_key` TEXT DEFAULT NULL COMMENT 'API Key',
    `username` VARCHAR(100) DEFAULT NULL COMMENT 'API Username',
    `password` VARCHAR(255) DEFAULT NULL COMMENT 'API Password (not encrypted)',
    `token` TEXT DEFAULT NULL COMMENT 'API Token',
    `client_id` VARCHAR(100) DEFAULT NULL COMMENT 'Client ID if required',
    `client_secret` TEXT DEFAULT NULL COMMENT 'Client Secret if required',
    `preference_order` INT(11) DEFAULT 0 COMMENT 'Order of preference (lower = higher priority)',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `remarks` TEXT DEFAULT NULL,
    `created_by` INT(11) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_by` INT(11) DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_preference_order` (`preference_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample courier partners
INSERT INTO `tbl_courier_partner` (`partner_name`, `partner_code`, `preference_order`, `status`, `remarks`) VALUES
('Delhivery B2B', 'DELHIVERY_B2B', 1, 'active', 'Business to Business deliveries'),
('Delhivery B2C', 'DELHIVERY_B2C', 2, 'active', 'Business to Customer deliveries'),
('Ekart Logistics', 'EKART', 3, 'active', 'Flipkart logistics partner'),
('Movin', 'MOVIN', 4, 'active', 'Express delivery service');
