-- Create Material Thickness Master Table
-- Run this in phpMyAdmin SQL tab

CREATE TABLE IF NOT EXISTS `material_thickness` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `thickness_code` VARCHAR(20) NOT NULL COMMENT 'Unique code for thickness (e.g., T1.0, T1.5, T2.0)',
  `thickness_value` DECIMAL(10,3) NOT NULL COMMENT 'Thickness value in mm',
  `thickness_unit` VARCHAR(10) NOT NULL DEFAULT 'mm' COMMENT 'Unit of measurement (default: mm)',
  `description` VARCHAR(200) NULL COMMENT 'Description (e.g., 1.0mm thickness)',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NULL COMMENT 'Display order in dropdowns',
  `created_by` INT NULL,
  `updated_by` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_thickness_company_code` (`company_id`, `thickness_code`),
  KEY `idx_thickness_company_active` (`company_id`, `is_active`),
  KEY `idx_thickness_value` (`thickness_value`),
  KEY `fk_thickness_company` (`company_id`),
  KEY `fk_thickness_created_by` (`created_by`),
  KEY `fk_thickness_updated_by` (`updated_by`),
  CONSTRAINT `fk_thickness_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `fk_thickness_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_thickness_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default thickness values
INSERT INTO `material_thickness` (`company_id`, `thickness_code`, `thickness_value`, `thickness_unit`, `description`, `is_active`, `sort_order`) VALUES
(1, 'T0.5', 0.500, 'mm', '0.5mm thickness', 1, 1),
(1, 'T0.8', 0.800, 'mm', '0.8mm thickness', 1, 2),
(1, 'T1.0', 1.000, 'mm', '1.0mm thickness', 1, 3),
(1, 'T1.2', 1.200, 'mm', '1.2mm thickness', 1, 4),
(1, 'T1.5', 1.500, 'mm', '1.5mm thickness', 1, 5),
(1, 'T2.0', 2.000, 'mm', '2.0mm thickness', 1, 6),
(1, 'T2.5', 2.500, 'mm', '2.5mm thickness', 1, 7),
(1, 'T3.0', 3.000, 'mm', '3.0mm thickness', 1, 8),
(1, 'T4.0', 4.000, 'mm', '4.0mm thickness', 1, 9),
(1, 'T5.0', 5.000, 'mm', '5.0mm thickness', 1, 10),
(1, 'T6.0', 6.000, 'mm', '6.0mm thickness', 1, 11),
(1, 'T8.0', 8.000, 'mm', '8.0mm thickness', 1, 12),
(1, 'T10.0', 10.000, 'mm', '10.0mm thickness', 1, 13),
(1, 'T12.0', 12.000, 'mm', '12.0mm thickness', 1, 14),
(1, 'T15.0', 15.000, 'mm', '15.0mm thickness', 1, 15),
(1, 'T20.0', 20.000, 'mm', '20.0mm thickness', 1, 16);

