-- Schema and seed for line_item_skins
CREATE TABLE IF NOT EXISTS `line_item_skins` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(32) NOT NULL,                 -- Skin Type, Tanning Type, Leather Tan Colour, Add-On
  `skin_type` VARCHAR(64) NULL,                    -- e.g., Flat skin, Back skin
  `tanning_type` VARCHAR(32) NULL,                 -- e.g., Leather tanning, Fur tanning
  `name` VARCHAR(255) NOT NULL,                    -- item/option name
  `notes` TEXT NULL,
  `add_on_price_zar` DECIMAL(10,2) NULL,
  `add_on_price_usd` DECIMAL(10,2) NULL,
  `add_on_price_eur` DECIMAL(10,2) NULL,
  `add_on_price_bwp` DECIMAL(10,2) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category_name` (`category`, `name`),
  KEY `idx_skin_tanning` (`skin_type`, `tanning_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed: Skin Types
INSERT INTO `line_item_skins`
(`category`, `skin_type`, `tanning_type`, `name`, `notes`, `add_on_price_zar`, `add_on_price_usd`, `add_on_price_eur`, `add_on_price_bwp`, `is_active`)
VALUES
('Skin Type', 'Flat skin', NULL, 'Flat skin', NULL, NULL, NULL, NULL, NULL, 1),
('Skin Type', 'Back skin', NULL, 'Back skin', NULL, NULL, NULL, NULL, NULL, 1);

-- Seed: Tanning Types
INSERT INTO `line_item_skins`
(`category`, `skin_type`, `tanning_type`, `name`, `notes`, `add_on_price_zar`, `add_on_price_usd`, `add_on_price_eur`, `add_on_price_bwp`, `is_active`)
VALUES
('Tanning Type', NULL, 'Leather tanning', 'Leather tanning', NULL, NULL, NULL, NULL, NULL, 1),
('Tanning Type', NULL, 'Fur tanning', 'Fur tanning', NULL, NULL, NULL, NULL, NULL, 1);

-- Seed: Leather Tan Colours (only for Leather tanning)
INSERT INTO `line_item_skins`
(`category`, `skin_type`, `tanning_type`, `name`, `notes`, `add_on_price_zar`, `add_on_price_usd`, `add_on_price_eur`, `add_on_price_bwp`, `is_active`)
VALUES
('Leather Tan Colour', NULL, 'Leather tanning', 'custom', 'client to define custom colour', NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Ice White', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'White', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Beige', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Oryx', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Serpentine', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Black', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Pink', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Orange', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Red', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Almond', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Turquoise', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Olive', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Lime Green', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Tabac', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Cognac', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Churchill Brown', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Kango Tabac', NULL, NULL, NULL, NULL, NULL, 1),
('Leather Tan Colour', NULL, 'Leather tanning', 'Nicotine', NULL, NULL, NULL, NULL, NULL, 1);

-- Seed: Add-Ons (for Fur tanning only)
INSERT INTO `line_item_skins`
(`category`, `skin_type`, `tanning_type`, `name`, `notes`, `add_on_price_zar`, `add_on_price_usd`, `add_on_price_eur`, `add_on_price_bwp`, `is_active`)
VALUES
('Add-On', NULL, 'Fur tanning', 'Felt lining (will need to tan skin & add felt) - for fur tanning only', NULL, NULL, NULL, NULL, NULL, 1);



