CALL `add_part_group`('chip_resistor', 'Chip Resistor - SMD', 'Surface mount resistors');

ALTER TABLE jegatroncomponents.group_details_chip_resistor ADD(
  `series` varchar(100) DEFAULT NULL,
  `resistance` float NOT NULL,
  `resistance_str` varchar(10) DEFAULT NULL,
  `resistance_tolerance_upper` float NOT NULL,
  `resistance_tolerance_lower` float NOT NULL,
  `power` float NOT NULL,
  `resistor_type` varchar(50) NOT NULL,
  `temperature_max` float NOT NULL,
  `temperature_min` float NOT NULL,
  `temperature_coefficient_upper` float NOT NULL,
  `temperature_coefficient_lower` float NOT NULL,
  `dimension_height` float NOT NULL,
  `dimension_width` float NOT NULL,
  `dimension_length` float NOT NULL,
  `component_package` varchar(50) NOT NULL,
  `component_symbol` varchar(50) NOT NULL,
  `number_of_terminals` int DEFAULT 2 NOT NULL,
  CONSTRAINT `group_details_chip_resistor_package_FK` FOREIGN KEY (`component_package`) REFERENCES `component_packages` (`component_package`) ON UPDATE CASCADE,
  CONSTRAINT `group_details_chip_resistor_symbol_FK` FOREIGN KEY (`component_symbol`) REFERENCES `component_symbols` (`component_symbol`) ON UPDATE CASCADE
);

delimiter //
CREATE OR REPLACE TRIGGER add_resistance_str
BEFORE INSERT ON jegatroncomponents.group_details_chip_resistor
FOR EACH ROW 
IF NEW.`resistance_str` IS NULL THEN
 SET NEW.resistance_str := to_value_string(NEW.resistance, 'R');
END IF;
//
CREATE OR REPLACE TRIGGER update_resistance_str
BEFORE UPDATE ON jegatroncomponents.group_details_chip_resistor
FOR EACH ROW 
IF NEW.`resistance_str` IS NULL THEN
 SET NEW.resistance_str := to_value_string(NEW.resistance, 'R');
END IF;
//
delimiter ;


INSERT INTO manufacturers (mfr) VALUES ('yageo');
INSERT INTO manufacturers (mfr) VALUES ('tdk');

DELETE FROM component_packages ;
INSERT INTO component_packages (component_package, package_display_name)
VALUES
('R01005', '01005 (0402 Metric)'),
('R0201', '0201 (0603 Metric)'),
('R0402', '0402 (1010 Metric)'),
('R0603', '0603 (1608 Metric)'),
('R0805', '0805 (2012 Metric)'),
('R1206', '1206 (3216 Metric)'),
('R1210', '1210 (3225 Metric)'),
('R1812', '1812 (4532 Metric)'),
('R2010', '2010 (5025 Metric)'),
('R2512', '2512 (6332 Metric)')
ON duplicate KEY UPDATE package_display_name = package_display_name;

DELETE FROM footprints_kicad ;
INSERT INTO footprints_kicad (component_package, kicad_footprint, kicad_footprint_filter)
VALUES
('R01005', 'Resistor_SMD:R_01005_0402Metric', 'Resistor_SMD:R_01005_*'),
('R0201', 'Resistor_SMD:R_0201_0603Metric', 'Resistor_SMD:R_0201_*'),
('R0402', 'Resistor_SMD:R_0402_1010Metric', 'Resistor_SMD:R_0402_*'),
('R0603', 'Resistor_SMD:R_0603_1608Metric', 'Resistor_SMD:R_0603_*'),
('R0805', 'Resistor_SMD:R_0805_2012Metric', 'Resistor_SMD:R_0805_*'),
('R1206', 'Resistor_SMD:R_1206_3216Metric', 'Resistor_SMD:R_1206_*'),
('R1210', 'Resistor_SMD:R_1210_3225Metric', 'Resistor_SMD:R_1210_*'),
('R1812', 'Resistor_SMD:R_1812_4532Metric', 'Resistor_SMD:R_1812_*'),
('R2010', 'Resistor_SMD:R_2010_5025Metric', 'Resistor_SMD:R_2010_*'),
('R2512', 'Resistor_SMD:R_2512_6332Metric', 'Resistor_SMD:R_2512_*')
ON duplicate KEY UPDATE kicad_footprint = kicad_footprint;


INSERT INTO component_symbols (component_symbol, symbol_display_name)
VALUES
('resistor_2pin', 'Resistor'),
('capacitor_2pin', 'Capacitor');

INSERT INTO symbols_kicad (component_symbol, kicad_symbol)
VALUES
('resistor_2pin', 'Device:R'),
('capacitor_2pin', 'Device:C');



DROP VIEW IF EXISTS kicad_resistors;
CREATE OR REPLACE VIEW kicad_resistors
AS
SELECT ip.internal_pn, internal_pn_str, ip.part_description AS description, gdcr.resistance AS value,
 cp.package_display_name, ip.exclude_from_bom, ip.exclude_from_board, fk.kicad_footprint,
 fk.kicad_footprint_filter, sk.kicad_symbol,
 gdcr.resistance_str AS value_str
FROM internal_parts ip
JOIN group_details_chip_resistor gdcr ON ip.internal_pn = gdcr.internal_pn
JOIN component_packages cp ON gdcr.component_package = cp.component_package
LEFT JOIN footprints_kicad fk ON gdcr.component_package = fk.component_package 
LEFT JOIN symbols_kicad sk ON gdcr.component_symbol = sk.component_symbol
WHERE ip.part_group_id = 'chip_resistor';

SELECT * FROM kicad_resistors kr ORDER BY internal_pn DESC;