-- ------------------------------------------------------------
-- Product Card Core v2 FULL
-- Install directly if Product Core v1 was NOT installed.
-- MySQL 5.7 compatible. Does not alter/drop existing CSV/order/stone data.
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS product_sample_materials (
    material_code VARCHAR(40) NOT NULL PRIMARY KEY,
    material_name VARCHAR(100) NOT NULL,
    density DECIMAL(10,4) NOT NULL,
    target_karat VARCHAR(20) NULL,
    is_sales_target TINYINT(1) NOT NULL DEFAULT 0,
    is_sample_material TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO product_sample_materials (material_code, material_name, density, target_karat, is_sales_target, is_sample_material, sort_order, is_active) VALUES
('10K_GREEN_GOLD', '10K Green Gold', 11.0300, '10K', 1, 1, 10, 1),
('14K_YELLOW_GOLD', '14K Yellow Gold', 13.0700, '14K', 1, 1, 20, 1),
('18K_YELLOW_GOLD', '18K Yellow Gold', 15.5800, '18K', 1, 1, 30, 1),
('AG_SILVER', 'AG Silver', 10.4900, 'Silver', 0, 1, 40, 1),
('BRONZE', 'Bronze', 8.8000, 'Bronze', 0, 1, 50, 1)
ON DUPLICATE KEY UPDATE
    material_name=VALUES(material_name), density=VALUES(density), target_karat=VALUES(target_karat),
    is_sales_target=VALUES(is_sales_target), is_sample_material=VALUES(is_sample_material), sort_order=VALUES(sort_order), is_active=VALUES(is_active);

CREATE TABLE IF NOT EXISTS product_core_specs (
    product_id INT UNSIGNED NOT NULL PRIMARY KEY,
    short_name VARCHAR(190) NULL,
    width_mm DECIMAL(10,3) NULL,
    length_mm DECIMAL(10,3) NULL,
    height_mm DECIMAL(10,3) NULL,
    band_width_mm DECIMAL(10,3) NULL,
    gram_10k DECIMAL(10,3) NULL,
    gram_14k DECIMAL(10,3) NULL,
    gram_18k DECIMAL(10,3) NULL,
    source_url TEXT NULL,
    sample_material_code VARCHAR(40) NULL,
    sample_karat VARCHAR(30) NULL,
    sample_color VARCHAR(50) NULL,
    sample_actual_gram DECIMAL(10,3) NULL,
    sample_stock_status VARCHAR(40) NOT NULL DEFAULT 'atelier',
    sample_owner_store_id INT UNSIGNED NULL,
    sample_charge_to_store TINYINT(1) NOT NULL DEFAULT 0,
    sample_note TEXT NULL,
    design_note TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_core_specs_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_core_specs_sample_store FOREIGN KEY (sample_owner_store_id) REFERENCES stores(id) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX idx_product_core_specs_sample_material (sample_material_code),
    INDEX idx_product_core_specs_sample_store (sample_owner_store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backward/partial install safety for sites that accidentally imported v1 before v2.
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product_core_specs' AND COLUMN_NAME='sample_material_code')=0,
'ALTER TABLE product_core_specs ADD COLUMN sample_material_code VARCHAR(40) NULL AFTER source_url', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product_core_specs' AND COLUMN_NAME='sample_stock_status')=0,
"ALTER TABLE product_core_specs ADD COLUMN sample_stock_status VARCHAR(40) NOT NULL DEFAULT 'atelier' AFTER sample_actual_gram", 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product_core_specs' AND COLUMN_NAME='sample_owner_store_id')=0,
'ALTER TABLE product_core_specs ADD COLUMN sample_owner_store_id INT UNSIGNED NULL AFTER sample_stock_status', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product_core_specs' AND COLUMN_NAME='sample_charge_to_store')=0,
'ALTER TABLE product_core_specs ADD COLUMN sample_charge_to_store TINYINT(1) NOT NULL DEFAULT 0 AFTER sample_owner_store_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS product_stone_slots (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    slot_no INT UNSIGNED NOT NULL DEFAULT 1,
    cut_id INT UNSIGNED NOT NULL,
    size_id INT UNSIGNED NOT NULL,
    qty_per_product INT UNSIGNED NOT NULL DEFAULT 1,
    slot_note VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_stone_slots_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_stone_slots_cut FOREIGN KEY (cut_id) REFERENCES stone_cuts(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_product_stone_slots_size FOREIGN KEY (size_id) REFERENCES stone_sizes(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_product_stone_slots_product (product_id),
    INDEX idx_product_stone_slots_cut_size (cut_id, size_id),
    INDEX idx_product_stone_slots_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_recipes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    recipe_code VARCHAR(10) NOT NULL,
    recipe_name VARCHAR(190) NULL,
    is_stone_free TINYINT(1) NOT NULL DEFAULT 0,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_recipes_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_recipes_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    UNIQUE KEY uq_product_recipe_code (product_id, recipe_code),
    INDEX idx_product_recipes_product (product_id),
    INDEX idx_product_recipes_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_recipe_stones (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipe_id INT UNSIGNED NOT NULL,
    product_stone_slot_id INT UNSIGNED NOT NULL,
    stone_id INT UNSIGNED NOT NULL,
    qty_per_product INT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_recipe_stones_recipe FOREIGN KEY (recipe_id) REFERENCES product_recipes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_recipe_stones_slot FOREIGN KEY (product_stone_slot_id) REFERENCES product_stone_slots(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_product_recipe_stones_stone FOREIGN KEY (stone_id) REFERENCES stones(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE KEY uq_recipe_slot (recipe_id, product_stone_slot_id),
    INDEX idx_product_recipe_stones_recipe (recipe_id),
    INDEX idx_product_recipe_stones_stone (stone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_store_recipe_skus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    recipe_id INT UNSIGNED NOT NULL,
    store_id INT UNSIGNED NOT NULL,
    sales_sku VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_store_recipe_skus_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_store_recipe_skus_recipe FOREIGN KEY (recipe_id) REFERENCES product_recipes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_store_recipe_skus_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY uq_product_store_recipe_sku (sales_sku),
    UNIQUE KEY uq_product_store_recipe (product_id, recipe_id, store_id),
    INDEX idx_product_store_recipe_skus_product (product_id),
    INDEX idx_product_store_recipe_skus_recipe (recipe_id),
    INDEX idx_product_store_recipe_skus_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_core_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id INT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    old_data TEXT NULL,
    new_data TEXT NULL,
    note TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_core_audit_entity (entity_type, entity_id),
    INDEX idx_product_core_audit_actor (actor_user_id),
    CONSTRAINT fk_product_core_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
