-- iloom Business Tables for muyu_wms Database
-- All tables use ilm_ prefix to coexist with muyu-apiserver tables
-- This script should be run AFTER muyu's init.sql

USE `muyu_wms`;

-- ==================== iloom Roles & Permissions ====================

INSERT IGNORE INTO `sys_role` (`role_id`, `role_name`, `role_key`, `role_desc`, `sort_order`, `status`) VALUES
('r_010', '采购商', 'purchaser', 'iloom purchaser role', 10, 1),
('r_011', '纺织厂', 'textile', 'iloom textile factory role', 11, 1),
('r_012', '水洗厂', 'washing', 'iloom washing factory role', 12, 1);

INSERT IGNORE INTO `casbin_rule` (`ptype`, `v0`, `v1`, `v2`) VALUES
('p', 'purchaser', '/api/v1/purchaser/*', '*'),
('p', 'purchaser', '/api/v1/common/*', '*'),
('p', 'purchaser', '/api/v1/auth/*', '*'),
('p', 'textile', '/api/v1/textile/*', '*'),
('p', 'textile', '/api/v1/common/*', '*'),
('p', 'textile', '/api/v1/auth/*', '*'),
('p', 'washing', '/api/v1/washing/*', '*'),
('p', 'washing', '/api/v1/common/*', '*'),
('p', 'washing', '/api/v1/auth/*', '*');

-- ==================== Company & Multi-tenancy ====================

CREATE TABLE IF NOT EXISTS `ilm_company` (
    `id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'purchaser|textile|washing',
    `address` VARCHAR(255) NOT NULL DEFAULT '',
    `phone` VARCHAR(20) NOT NULL DEFAULT '',
    `contact_person` VARCHAR(50) NOT NULL DEFAULT '',
    `status` TINYINT NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_company_member` (
    `id` VARCHAR(36) NOT NULL,
    `company_id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(32) NOT NULL,
    `role` VARCHAR(20) NOT NULL DEFAULT 'member' COMMENT 'owner|admin|member',
    `is_master` TINYINT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_company_user` (`company_id`, `user_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_company_relationship` (
    `id` VARCHAR(36) NOT NULL,
    `from_company_id` VARCHAR(36) NOT NULL,
    `to_company_id` VARCHAR(36) NOT NULL,
    `relationship_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'supplier|customer|partner',
    `status` VARCHAR(20) NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_relationship` (`from_company_id`, `to_company_id`, `relationship_type`),
    KEY `idx_to_company` (`to_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Product Extension ====================

CREATE TABLE IF NOT EXISTS `ilm_product_ext` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `product_id` VARCHAR(32) NOT NULL,
    `company_id` VARCHAR(36) NOT NULL,
    `fabric_code` VARCHAR(50) NOT NULL DEFAULT '',
    `color_code` VARCHAR(50) NOT NULL DEFAULT '',
    `yarn_usage_per_meter` DECIMAL(10,4) DEFAULT NULL,
    `current_price` DECIMAL(10,2) DEFAULT NULL,
    `warp_weight` DECIMAL(10,2) DEFAULT NULL,
    `weft_weight` DECIMAL(10,2) DEFAULT NULL,
    `total_weight` DECIMAL(10,2) DEFAULT NULL,
    `stock_meters` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `stock_rolls` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_product_company` (`product_id`, `company_id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Production Plans ====================

CREATE TABLE IF NOT EXISTS `ilm_production_plan` (
    `id` VARCHAR(36) NOT NULL,
    `plan_code` VARCHAR(50) NOT NULL,
    `purchaser_id` VARCHAR(36) NOT NULL COMMENT 'company_id of purchaser',
    `product_name` VARCHAR(100) NOT NULL DEFAULT '',
    `fabric_code` VARCHAR(50) NOT NULL DEFAULT '',
    `color` VARCHAR(50) NOT NULL DEFAULT '',
    `color_code` VARCHAR(50) NOT NULL DEFAULT '',
    `target_quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `completed_quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `production_price` DECIMAL(10,2) DEFAULT NULL,
    `yarn_usage_per_meter` DECIMAL(10,4) DEFAULT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT 'draft|producing|completed|cancelled',
    `remark` TEXT,
    `start_time` DATETIME DEFAULT NULL,
    `created_by` VARCHAR(32) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_plan_code` (`plan_code`),
    KEY `idx_purchaser` (`purchaser_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_plan_factory` (
    `id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL,
    `factory_id` VARCHAR(36) NOT NULL,
    `factory_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'textile|washing',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_plan` (`plan_id`),
    KEY `idx_factory` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_yarn_ratio` (
    `id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL,
    `yarn_name` VARCHAR(100) NOT NULL DEFAULT '',
    `yarn_type` VARCHAR(50) NOT NULL DEFAULT '',
    `ratio` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `amount_per_meter` DECIMAL(10,4) DEFAULT NULL,
    `total_amount` DECIMAL(12,2) DEFAULT NULL,
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Production Process ====================

CREATE TABLE IF NOT EXISTS `ilm_process_step` (
    `id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL,
    `step_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'warping|sizing|weaving|inspection|finishing',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|in_progress|completed|rejected',
    `notes` TEXT,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `timestamp` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_plan` (`plan_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Warehousing ====================

CREATE TABLE IF NOT EXISTS `ilm_warehouse` (
    `id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `location` VARCHAR(255) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL,
    `capacity` DECIMAL(12,2) DEFAULT NULL,
    `status` TINYINT NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_inventory_record` (
    `id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `warehouse_id` VARCHAR(36) NOT NULL DEFAULT '',
    `quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `rolls` INT DEFAULT NULL,
    `price_per_meter` DECIMAL(10,2) DEFAULT NULL,
    `price_note` TEXT,
    `warehouse_location` VARCHAR(100) NOT NULL DEFAULT '',
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `record_type` VARCHAR(20) NOT NULL DEFAULT 'inbound' COMMENT 'inbound|outbound',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_plan` (`plan_id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Product Inventory (Purchaser) ====================

CREATE TABLE IF NOT EXISTS `ilm_product_inventory` (
    `id` VARCHAR(36) NOT NULL,
    `product_id` VARCHAR(32) NOT NULL DEFAULT '',
    `batch_no` VARCHAR(50) NOT NULL DEFAULT '',
    `rolls` INT NOT NULL DEFAULT 0,
    `meters` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `record_type` VARCHAR(20) NOT NULL DEFAULT 'inbound' COMMENT 'inbound|outbound',
    `notes` TEXT,
    `warehouse_id` VARCHAR(36) NOT NULL DEFAULT '',
    `plan_code` VARCHAR(50) NOT NULL DEFAULT '',
    `factory_id` VARCHAR(36) NOT NULL DEFAULT '',
    `source` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'self|textile',
    `price_per_meter` DECIMAL(10,2) DEFAULT NULL,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_product` (`product_id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_product_outbound` (
    `id` VARCHAR(36) NOT NULL,
    `product_id` VARCHAR(32) NOT NULL DEFAULT '',
    `batch_no` VARCHAR(50) NOT NULL DEFAULT '',
    `rolls` INT NOT NULL DEFAULT 0,
    `meters` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `outbound_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'sale|washing|transfer',
    `notes` TEXT,
    `washing_plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `recipient_company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_product` (`product_id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Price History ====================

CREATE TABLE IF NOT EXISTS `ilm_product_price_history` (
    `id` VARCHAR(36) NOT NULL,
    `product_id` VARCHAR(32) NOT NULL DEFAULT '',
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `reason` TEXT,
    `changed_by` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_production_price_history` (
    `id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `reason` TEXT,
    `changed_by` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Accounts Payable ====================

CREATE TABLE IF NOT EXISTS `ilm_accounts_payable` (
    `id` VARCHAR(36) NOT NULL,
    `purchaser_id` VARCHAR(36) NOT NULL,
    `factory_id` VARCHAR(36) NOT NULL,
    `factory_type` VARCHAR(20) NOT NULL DEFAULT '',
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|partial|paid',
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_purchaser` (`purchaser_id`),
    KEY `idx_factory` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_ap_item` (
    `id` VARCHAR(36) NOT NULL,
    `payable_id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `description` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_payable` (`payable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Washing Plans ====================

CREATE TABLE IF NOT EXISTS `ilm_washing_plan` (
    `id` VARCHAR(36) NOT NULL,
    `plan_code` VARCHAR(50) NOT NULL DEFAULT '',
    `purchaser_id` VARCHAR(36) NOT NULL,
    `washing_factory_id` VARCHAR(36) NOT NULL DEFAULT '',
    `product_name` VARCHAR(100) NOT NULL DEFAULT '',
    `fabric_code` VARCHAR(50) NOT NULL DEFAULT '',
    `color` VARCHAR(50) NOT NULL DEFAULT '',
    `quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `unit_price` DECIMAL(10,2) DEFAULT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|accepted|processing|completed|rejected',
    `notes` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_purchaser` (`purchaser_id`),
    KEY `idx_washing_factory` (`washing_factory_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Washing Process ====================

CREATE TABLE IF NOT EXISTS `ilm_washing_step` (
    `id` VARCHAR(36) NOT NULL,
    `washing_plan_id` VARCHAR(36) NOT NULL,
    `step_type` VARCHAR(30) NOT NULL DEFAULT '',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `notes` TEXT,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `timestamp` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_washing_plan` (`washing_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_washing_completion` (
    `id` VARCHAR(36) NOT NULL,
    `washing_plan_id` VARCHAR(36) NOT NULL,
    `completed_quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `quality_notes` TEXT,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `completed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_washing_plan` (`washing_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Finished Products ====================

CREATE TABLE IF NOT EXISTS `ilm_finished_product` (
    `id` VARCHAR(36) NOT NULL,
    `product_name` VARCHAR(100) NOT NULL DEFAULT '',
    `fabric_code` VARCHAR(50) NOT NULL DEFAULT '',
    `color` VARCHAR(50) NOT NULL DEFAULT '',
    `stock_meters` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `stock_rolls` INT NOT NULL DEFAULT 0,
    `company_id` VARCHAR(36) NOT NULL,
    `washing_plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_finished_product_inventory` (
    `id` VARCHAR(36) NOT NULL,
    `finished_product_id` VARCHAR(36) NOT NULL,
    `batch_no` VARCHAR(50) NOT NULL DEFAULT '',
    `rolls` INT NOT NULL DEFAULT 0,
    `meters` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `record_type` VARCHAR(20) NOT NULL DEFAULT 'inbound',
    `notes` TEXT,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_finished_product` (`finished_product_id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Yarn Stock ====================

CREATE TABLE IF NOT EXISTS `ilm_yarn_stock` (
    `id` VARCHAR(36) NOT NULL,
    `yarn_name` VARCHAR(100) NOT NULL DEFAULT '',
    `yarn_type` VARCHAR(50) NOT NULL DEFAULT '',
    `current_stock` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `unit` VARCHAR(20) NOT NULL DEFAULT 'kg',
    `company_id` VARCHAR(36) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_yarn_stock_record` (
    `id` VARCHAR(36) NOT NULL,
    `yarn_stock_id` VARCHAR(36) NOT NULL,
    `quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `record_type` VARCHAR(20) NOT NULL DEFAULT 'in' COMMENT 'in|out',
    `notes` TEXT,
    `operator_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_yarn_stock` (`yarn_stock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Payments ====================

CREATE TABLE IF NOT EXISTS `ilm_payment` (
    `id` VARCHAR(36) NOT NULL,
    `payer_company_id` VARCHAR(36) NOT NULL,
    `payee_company_id` VARCHAR(36) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'production|washing',
    `plan_id` VARCHAR(36) NOT NULL DEFAULT '',
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending|confirmed|rejected',
    `notes` TEXT,
    `confirmed_at` DATETIME DEFAULT NULL,
    `confirmed_by` VARCHAR(32) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_payer` (`payer_company_id`),
    KEY `idx_payee` (`payee_company_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== Notifications & Misc ====================

CREATE TABLE IF NOT EXISTS `ilm_notification` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(32) NOT NULL,
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `title` VARCHAR(200) NOT NULL DEFAULT '',
    `message` TEXT,
    `type` VARCHAR(30) NOT NULL DEFAULT 'info' COMMENT 'info|warning|success|error',
    `is_read` TINYINT NOT NULL DEFAULT 0,
    `link` VARCHAR(255) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_unread` (`user_id`, `is_read`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_share_link` (
    `id` VARCHAR(36) NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `company_id` VARCHAR(36) NOT NULL,
    `created_by` VARCHAR(32) NOT NULL DEFAULT '',
    `resource_type` VARCHAR(30) NOT NULL DEFAULT '',
    `resource_id` VARCHAR(36) NOT NULL DEFAULT '',
    `expires_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`),
    KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ilm_audit_log` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(32) NOT NULL DEFAULT '',
    `company_id` VARCHAR(36) NOT NULL DEFAULT '',
    `action` VARCHAR(50) NOT NULL DEFAULT '',
    `resource_type` VARCHAR(50) NOT NULL DEFAULT '',
    `resource_id` VARCHAR(36) NOT NULL DEFAULT '',
    `old_value` JSON DEFAULT NULL,
    `new_value` JSON DEFAULT NULL,
    `ip` VARCHAR(50) NOT NULL DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_company` (`company_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;