-- Create Material Sheet Type Master Table
-- Run this in phpMyAdmin SQL tab

CREATE TABLE IF NOT EXISTS `material_sheet_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `sheet_type_code` VARCHAR(20) NOT NULL COMMENT 'Unique code for sheet type (e.g., SHEET, COIL, PLATE)',
  `sheet_type_name` VARCHAR(100) NOT NULL COMMENT 'Name of sheet type (e.g., Sheet, Coil, Plate)',
  `description` VARCHAR(500) NULL COMMENT 'Description of sheet type',
  `standard_width` DECIMAL(10,3) NULL COMMENT 'Standard width in mm (if applicable)',
  `standard_length` DECIMAL(10,3) NULL COMMENT 'Standard length in mm (if applicable)',
  `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_sheet_type_company_code` (`company_id`, `sheet_type_code`),
  KEY `idx_sheet_type_company_active` (`company_id`, `is_active`),
  KEY `fk_sheet_type_company` (`company_id`),
  KEY `fk_sheet_type_created_by` (`created_by`),
  KEY `fk_sheet_type_updated_by` (`updated_by`),
  CONSTRAINT `fk_sheet_type_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `fk_sheet_type_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_sheet_type_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default sheet types
INSERT INTO `material_sheet_type` (`company_id`, `sheet_type_code`, `sheet_type_name`, `description`, `standard_width`, `standard_length`, `is_active`, `sort_order`) VALUES
(1, 'SHEET', 'Sheet', 'Flat metal sheet', NULL, NULL, 1, 1),
(1, 'COIL', 'Coil', 'Rolled metal coil', NULL, NULL, 1, 2),
(1, 'PLATE', 'Plate', 'Thick metal plate', NULL, NULL, 1, 3),
(1, 'STRIP', 'Strip', 'Narrow metal strip', NULL, NULL, 1, 4),
(1, 'ROLL', 'Roll', 'Rolled metal', NULL, NULL, 1, 5),
(1, 'FOIL', 'Foil', 'Thin metal foil', NULL, NULL, 1, 6),
(1, 'PIPE', 'Pipe', 'Hollow cylindrical pipe', NULL, NULL, 1, 7),
(1, 'TUBE', 'Tube', 'Hollow cylindrical tube', NULL, NULL, 1, 8),
(1, 'ANGLE', 'Angle', 'L-shaped angle section', NULL, NULL, 1, 9),
(1, 'CHANNEL', 'Channel', 'C-shaped channel section', NULL, NULL, 1, 10),
(1, 'ROUND_BAR', 'Round Bar', 'Solid round bar', NULL, NULL, 1, 11),
(1, 'FLAT_BAR', 'Flat Bar', 'Solid flat bar', NULL, NULL, 1, 12),
(1, 'SQUARE_BAR', 'Square Bar', 'Solid square bar', NULL, NULL, 1, 13);

