-- ============================================================
-- Migracja 004: Strona główna premium + Usługi + Strony lokalne SEO
-- Tartak Dana CMS - Etap 3
-- Kompatybilność: MySQL 8.0
-- ============================================================


-- -----------------------------------------------------------
-- Kategorie: flaga wyświetlania na stronie głównej
-- -----------------------------------------------------------
SET @has_show_on_home := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'categories'
      AND COLUMN_NAME = 'show_on_home'
);
SET @sql := IF(
    @has_show_on_home = 0,
    'ALTER TABLE `categories` ADD COLUMN `show_on_home` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_visible`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_home_visible_sort := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'categories'
      AND INDEX_NAME = 'idx_home_visible_sort'
);
SET @sql := IF(
    @has_idx_home_visible_sort = 0,
    'ALTER TABLE `categories` ADD INDEX `idx_home_visible_sort` (`show_on_home`, `is_visible`, `sort_order`)',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ustaw domyślnie pierwsze 6 kategorii głównych jako widoczne na home
UPDATE `categories`
SET `show_on_home` = 1
WHERE `id` IN (
    SELECT id FROM (
        SELECT `id`
        FROM `categories`
        WHERE `parent_id` IS NULL
        ORDER BY `sort_order` ASC, `name` ASC
        LIMIT 6
    ) t
);

-- -----------------------------------------------------------
-- Usługi
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `services` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `title`            VARCHAR(200)    NOT NULL,
    `slug`             VARCHAR(220)    NOT NULL,
    `excerpt`          VARCHAR(300)    NOT NULL DEFAULT '',
    `content_html`     TEXT            NULL,
    `meta_title`       VARCHAR(70)     NOT NULL DEFAULT '',
    `meta_description` VARCHAR(160)    NOT NULL DEFAULT '',
    `is_published`     TINYINT(1)      NOT NULL DEFAULT 0,
    `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_services_slug` (`slug`),
    INDEX `idx_services_published_sort` (`is_published`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service_faq` (
    `id`           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `service_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_service_faq` (`service_id`, `sort_order`),
    CONSTRAINT `fk_service_faq_service` FOREIGN KEY (`service_id`)
        REFERENCES `services`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Strony informacyjne (home + local SEO)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pages` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `title`            VARCHAR(200)    NOT NULL,
    `slug`             VARCHAR(220)    NOT NULL,
    `page_type`        ENUM('home','local','info') NOT NULL DEFAULT 'info',
    `content_html`     LONGTEXT        NULL,
    `meta_title`       VARCHAR(70)     NOT NULL DEFAULT '',
    `meta_description` VARCHAR(160)    NOT NULL DEFAULT '',
    `settings_json`    LONGTEXT        NULL,
    `is_published`     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_pages_slug` (`slug`),
    INDEX `idx_pages_type_published` (`page_type`, `is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `pages` (`title`, `slug`, `page_type`, `content_html`, `meta_title`, `meta_description`, `settings_json`, `is_published`)
VALUES
    (
        'Strona główna',
        'home',
        'home',
        '',
        'Tartak Dana - deski elewacyjne, drewno konstrukcyjne | Ełk',
        'Tartak Dana w Stradunach k. Ełku. Deski elewacyjne, podbitka dachowa, drewno konstrukcyjne C24, deski tarasowe i podłogowe.',
        JSON_OBJECT(),
        1
    ),
    (
        'Tartak Ełk',
        'elk-tartak',
        'local',
        '<p>Obsługujemy klientów z Ełku i okolic. Oferujemy drewno budowlane oraz wykończeniowe z szybką dostawą.</p>',
        'Tartak Ełk - drewno budowlane i wykończeniowe | Tartak Dana',
        'Tartak Dana: drewno budowlane i wykończeniowe dla klientów z Ełku i okolic. Produkcja, doradztwo i transport.',
        JSON_OBJECT(),
        1
    ),
    (
        'Drewno Warmia i Mazury',
        'warmia-mazury-drewno',
        'local',
        '<p>Realizujemy zamówienia na terenie Warmii i Mazur. Dostarczamy drewno dla budownictwa i wykończeń.</p>',
        'Drewno Warmia i Mazury - producent i transport | Tartak Dana',
        'Tartak Dana dostarcza drewno budowlane i wykończeniowe na terenie Warmii i Mazur. Zapytaj o transport i wycenę.',
        JSON_OBJECT(),
        1
    )
ON DUPLICATE KEY UPDATE
    `title` = VALUES(`title`),
    `page_type` = VALUES(`page_type`),
    `is_published` = VALUES(`is_published`);
