-- ------------------------------------------------------------
-- Product Card Core v3 PATCH
-- Install after product_core_v2_full.sql.
-- MySQL 5.7 compatible.
-- Adds managed product types, sample photo-on-create support tables,
-- multi-store product usage and multi-store sample cost allocation.
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS product_types (
    category_code VARCHAR(50) NOT NULL PRIMARY KEY,
    type_label VARCHAR(100) NOT NULL,
    sku_prefix VARCHAR(10) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    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,
    UNIQUE KEY uq_product_type_prefix (sku_prefix)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO product_types (category_code, type_label, sku_prefix, sort_order, is_active) VALUES
('ring', 'Yüzük', 'R', 10, 1),
('pendant', 'Kolye / Pendant', 'P', 20, 1),
('earring', 'Küpe', 'E', 30, 1),
('bracelet', 'Bileklik', 'B', 40, 1),
('anklet', 'Halhal', 'H', 50, 1),
('shahmeran', 'Şahmeran', 'SH', 60, 1),
('piercing', 'Piercing', 'PI', 70, 1),
('other', 'Diğer', 'O', 999, 1)
ON DUPLICATE KEY UPDATE
    type_label=VALUES(type_label),
    sku_prefix=VALUES(sku_prefix),
    sort_order=VALUES(sort_order),
    is_active=VALUES(is_active),
    updated_at=NOW();

-- Screen labels should be simple; densities remain internal calculation data.
UPDATE product_sample_materials SET material_name='10K', target_karat='10K', density=11.0300 WHERE material_code='10K_GREEN_GOLD';
UPDATE product_sample_materials SET material_name='14K', target_karat='14K', density=13.0700 WHERE material_code='14K_YELLOW_GOLD';
UPDATE product_sample_materials SET material_name='18K', target_karat='18K', density=15.5800 WHERE material_code='18K_YELLOW_GOLD';
UPDATE product_sample_materials SET material_name='AG Silver', target_karat='Silver', density=10.4900 WHERE material_code='AG_SILVER';
UPDATE product_sample_materials SET material_name='Bronze', target_karat='Bronze', density=8.8000 WHERE material_code='BRONZE';

CREATE TABLE IF NOT EXISTS product_store_usage (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    store_id INT UNSIGNED NOT NULL,
    added_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_store_usage (product_id, store_id),
    INDEX idx_product_store_usage_store (store_id),
    CONSTRAINT fk_product_store_usage_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_store_usage_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_store_usage_user FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_sample_charge_stores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    store_id INT UNSIGNED NOT NULL,
    allocation_percent DECIMAL(8,4) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_sample_charge_store (product_id, store_id),
    INDEX idx_product_sample_charge_store (store_id),
    CONSTRAINT fk_product_sample_charge_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_product_sample_charge_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
