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

CREATE TABLE IF NOT EXISTS `material_grade` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `grade_code` VARCHAR(20) NOT NULL COMMENT 'Unique code for material grade (e.g., MS, GI, SS304, SS316, AL)',
  `grade_name` VARCHAR(100) NOT NULL COMMENT 'Name of material grade (e.g., Mild Steel, Galvanized Iron)',
  `description` VARCHAR(500) NULL COMMENT 'Detailed description of the grade',
  `specification` VARCHAR(200) NULL COMMENT 'Industry specification (e.g., IS 2062, ASTM A36)',
  `properties` TEXT NULL COMMENT 'Material properties in JSON format',
  `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_grade_company_code` (`company_id`, `grade_code`),
  KEY `idx_grade_company_active` (`company_id`, `is_active`),
  KEY `fk_grade_company` (`company_id`),
  KEY `fk_grade_created_by` (`created_by`),
  KEY `fk_grade_updated_by` (`updated_by`),
  CONSTRAINT `fk_grade_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `fk_grade_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_grade_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default material grades
INSERT INTO `material_grade` (`company_id`, `grade_code`, `grade_name`, `description`, `specification`, `is_active`, `sort_order`) VALUES
(1, 'MS', 'Mild Steel', 'Low carbon steel, easy to weld and form', 'IS 2062', 1, 1),
(1, 'GI', 'Galvanized Iron', 'Zinc-coated steel for corrosion resistance', 'IS 277', 1, 2),
(1, 'SS304', 'Stainless Steel 304', 'Austenitic stainless steel, general purpose', 'ASTM A240', 1, 3),
(1, 'SS316', 'Stainless Steel 316', 'Austenitic stainless steel, superior corrosion resistance', 'ASTM A240', 1, 4),
(1, 'AL', 'Aluminum', 'Lightweight metal, good conductivity', 'IS 737', 1, 5),
(1, 'CRCA', 'Cold Rolled Close Annealed', 'Cold rolled steel, smooth finish', 'IS 513', 1, 6),
(1, 'HRCA', 'Hot Rolled Close Annealed', 'Hot rolled steel, rough finish', 'IS 1079', 1, 7),
(1, 'CU', 'Copper', 'High conductivity metal', 'IS 191', 1, 8),
(1, 'BR', 'Brass', 'Copper-zinc alloy', 'IS 319', 1, 9),
(1, 'SS201', 'Stainless Steel 201', 'Austenitic stainless steel, economical', 'ASTM A240', 1, 10);

