-- ============================================================
-- Migracja 002: Kategorie + Produkty + relacje
-- Tartak Dana CMS — Etap 1 (Oferta)
-- Kompatybilność: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------
-- Tabela: categories
-- Hierarchia: parent_id → self-reference (kategorie nadrzędne)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(200)    NOT NULL,
    `slug`             VARCHAR(220)    NOT NULL,
    `parent_id`        INT UNSIGNED    DEFAULT NULL,
    `description_seo`  TEXT            NULL COMMENT 'Opis merytoryczno-poradnikowy (WYSIWYG HTML) pod listą produktów',
    `meta_title`       VARCHAR(70)     NOT NULL DEFAULT '',
    `meta_description` VARCHAR(160)    NOT NULL DEFAULT '',
    `image_path`       VARCHAR(255)    DEFAULT NULL,
    `is_visible`       TINYINT(1)      NOT NULL DEFAULT 1,
    `sort_order`       INT             NOT NULL DEFAULT 0,
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_categories_slug` (`slug`),
    INDEX `idx_parent` (`parent_id`),
    INDEX `idx_visible_sort` (`is_visible`, `sort_order`),
    CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`)
        REFERENCES `categories`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: products
-- Parametry techniczne jako kolumny (stały zestaw wg koncepcji).
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
    `id`                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`                VARCHAR(200)    NOT NULL,
    `slug`                VARCHAR(220)    NOT NULL,
    `category_id`         INT UNSIGNED    NOT NULL COMMENT 'Kategoria główna',
    `lead`                VARCHAR(300)    NOT NULL DEFAULT '' COMMENT 'Krótki opis / lead',
    `description`         TEXT            NULL COMMENT 'Opis główny (WYSIWYG HTML)',
    `tips_html`           TEXT            NULL COMMENT 'Wskazówki praktyczne (WYSIWYG HTML)',

    -- Parametry techniczne
    `wood_species`        VARCHAR(50)     NOT NULL DEFAULT '' COMMENT 'Gatunek drewna',
    `thickness_mm`        VARCHAR(20)     NOT NULL DEFAULT '' COMMENT 'Grubość (mm)',
    `width_useful_mm`     VARCHAR(20)     NOT NULL DEFAULT '' COMMENT 'Szerokość użytkowa (mm)',
    `width_total_mm`      VARCHAR(20)     NOT NULL DEFAULT '' COMMENT 'Szerokość całkowita (mm)',
    `length_from_m`       VARCHAR(10)     NOT NULL DEFAULT '' COMMENT 'Długość od (m)',
    `length_to_m`         VARCHAR(10)     NOT NULL DEFAULT '' COMMENT 'Długość do (m)',
    `profile`             VARCHAR(60)     NOT NULL DEFAULT '' COMMENT 'Profil',
    `moisture`            VARCHAR(30)     NOT NULL DEFAULT '' COMMENT 'Wilgotność',
    `treatment`           VARCHAR(50)     NOT NULL DEFAULT '' COMMENT 'Obróbka',
    `impregnation`        VARCHAR(30)     NOT NULL DEFAULT '' COMMENT 'Impregnacja',
    `wood_class`          VARCHAR(10)     NOT NULL DEFAULT '' COMMENT 'Klasa drewna',
    `strength_class`      VARCHAR(10)     NOT NULL DEFAULT '' COMMENT 'Klasa wytrzymałości',
    `standard`            VARCHAR(50)     NOT NULL DEFAULT '' COMMENT 'Norma',
    `certificate`         VARCHAR(20)     NOT NULL DEFAULT '' COMMENT 'Certyfikat',
    `weight_approx`       VARCHAR(30)     NOT NULL DEFAULT '' COMMENT 'Waga orientacyjna',
    `unit`                VARCHAR(10)     NOT NULL DEFAULT 'szt.' COMMENT 'Jednostka sprzedaży',

    -- Dostępność
    `availability_status` ENUM('available','on_order','unavailable') NOT NULL DEFAULT 'available',
    `lead_time_text`      VARCHAR(100)    NOT NULL DEFAULT '' COMMENT 'Czas realizacji',

    -- Publikacja
    `status`              ENUM('published','draft','hidden') NOT NULL DEFAULT 'draft',
    `is_featured`         TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Polecany (strona główna)',
    `sort_order`          INT             NOT NULL DEFAULT 0,

    -- Zdjęcia (TODO: pełny uploader w Etapie 2)
    `main_image_path`     VARCHAR(255)    DEFAULT NULL,

    -- SEO
    `meta_title`          VARCHAR(70)     NOT NULL DEFAULT '',
    `meta_description`    VARCHAR(160)    NOT NULL DEFAULT '',
    `is_indexable`        TINYINT(1)      NOT NULL DEFAULT 1,

    `created_at`          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_products_slug` (`slug`),
    INDEX `idx_category` (`category_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_featured` (`is_featured`),
    INDEX `idx_sort` (`sort_order`),
    CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`)
        REFERENCES `categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: product_categories (many-to-many — dodatkowe kategorie)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
    `product_id`   INT UNSIGNED NOT NULL,
    `category_id`  INT UNSIGNED NOT NULL,
    PRIMARY KEY (`product_id`, `category_id`),
    CONSTRAINT `fk_pc_product`  FOREIGN KEY (`product_id`)  REFERENCES `products`(`id`)    ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_pc_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: product_faq
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_faq` (
    `id`           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `product_id`   INT UNSIGNED    NOT NULL,
    `question`     VARCHAR(500)    NOT NULL,
    `answer`       TEXT            NOT NULL,
    `sort_order`   INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `idx_product_faq` (`product_id`, `sort_order`),
    CONSTRAINT `fk_faq_product` FOREIGN KEY (`product_id`)
        REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
