-- ============================================================
-- Migracja 005: Strony info (O firmie + FAQ), FAQ, formularz wyceny 2.0
-- Tartak Dana CMS - Etap 4
-- Kompatybilność: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------
-- Strony informacyjne (info): o-firmie + faq
-- -----------------------------------------------------------
INSERT INTO `pages` (`title`, `slug`, `page_type`, `content_html`, `meta_title`, `meta_description`, `settings_json`, `is_published`)
VALUES
    (
        'O firmie',
        'o-firmie',
        'info',
        '<p>Tartak Dana to firma rodzinna działająca od 2000 roku w Stradunach k. Ełku. Łączymy doświadczenie, nowoczesny park maszynowy i rzetelne doradztwo.</p>',
        'O firmie - Tartak Dana | Straduny k. Ełku',
        'Poznaj Tartak Dana: historię firmy, wartości, park maszynowy i zasięg działania w regionie Ełku, Warmii i Mazur.',
        JSON_OBJECT(),
        1
    ),
    (
        'FAQ',
        'faq',
        'info',
        '<p>Odpowiedzi na najczęściej zadawane pytania dotyczące produktów, usług, transportu i realizacji zamówień.</p>',
        'FAQ - najczęściej zadawane pytania | Tartak Dana',
        'Sprawdź odpowiedzi na najczęstsze pytania klientów Tartaku Dana: produkty, usługi, transport, zamówienia i płatności.',
        JSON_OBJECT(),
        1
    )
ON DUPLICATE KEY UPDATE
    `title` = VALUES(`title`),
    `page_type` = VALUES(`page_type`),
    `is_published` = VALUES(`is_published`);

-- -----------------------------------------------------------
-- Ustawienia firmy: URL osadzenia mapy Google (kontakt)
-- -----------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`)
VALUES ('google_maps_embed_url', '')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- -----------------------------------------------------------
-- FAQ: kategorie i pytania
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `faq_categories` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `title`         VARCHAR(120)    NOT NULL,
    `slug`          VARCHAR(140)    NOT NULL,
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `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_faq_categories_slug` (`slug`),
    INDEX `idx_faq_categories_pub_sort` (`is_published`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `faq_items` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `category_id`   INT UNSIGNED    NOT NULL,
    `question`      VARCHAR(500)    NOT NULL,
    `answer_html`   TEXT            NOT NULL,
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `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`),
    INDEX `idx_faq_items_category_pub_sort` (`category_id`, `is_published`, `sort_order`),
    CONSTRAINT `fk_faq_items_category` FOREIGN KEY (`category_id`)
        REFERENCES `faq_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `faq_categories` (`title`, `slug`, `sort_order`, `is_published`)
VALUES
    ('Produkty', 'produkty', 10, 1),
    ('Usługi', 'uslugi', 20, 1),
    ('Transport', 'transport', 30, 1),
    ('Zamówienia i płatności', 'zamowienia-platnosci', 40, 1)
ON DUPLICATE KEY UPDATE
    `title` = VALUES(`title`),
    `sort_order` = VALUES(`sort_order`),
    `is_published` = VALUES(`is_published`);

-- -----------------------------------------------------------
-- Zapytania (CRM-lite): pola formularza wyceny 2.0
-- -----------------------------------------------------------
SET @has_service_title := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND COLUMN_NAME = 'service_title'
);
SET @sql := IF(
    @has_service_title = 0,
    'ALTER TABLE `inquiries` ADD COLUMN `service_title` VARCHAR(200) NULL AFTER `realization_id`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_inquiry_category_id := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND COLUMN_NAME = 'inquiry_category_id'
);
SET @sql := IF(
    @has_inquiry_category_id = 0,
    'ALTER TABLE `inquiries` ADD COLUMN `inquiry_category_id` INT UNSIGNED NULL AFTER `product_id`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_dimensions_text := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND COLUMN_NAME = 'dimensions_text'
);
SET @sql := IF(
    @has_dimensions_text = 0,
    'ALTER TABLE `inquiries` ADD COLUMN `dimensions_text` VARCHAR(120) NULL AFTER `quantity_unit`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_needs_transport := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND COLUMN_NAME = 'needs_transport'
);
SET @sql := IF(
    @has_needs_transport = 0,
    'ALTER TABLE `inquiries` ADD COLUMN `needs_transport` TINYINT(1) NOT NULL DEFAULT 0 AFTER `dimensions_text`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_delivery_address := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND COLUMN_NAME = 'delivery_address'
);
SET @sql := IF(
    @has_delivery_address = 0,
    'ALTER TABLE `inquiries` ADD COLUMN `delivery_address` VARCHAR(255) NULL AFTER `needs_transport`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_inquiry_category := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND INDEX_NAME = 'idx_inquiry_category'
);
SET @sql := IF(
    @has_idx_inquiry_category = 0,
    'ALTER TABLE `inquiries` ADD INDEX `idx_inquiry_category` (`inquiry_category_id`)',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_service_title := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inquiries'
      AND INDEX_NAME = 'idx_service_title'
);
SET @sql := IF(
    @has_idx_service_title = 0,
    'ALTER TABLE `inquiries` ADD INDEX `idx_service_title` (`service_title`)',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_fk_inquiry_category := (
    SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND CONSTRAINT_NAME = 'fk_inquiry_category'
);
SET @sql := IF(
    @has_fk_inquiry_category = 0,
    'ALTER TABLE `inquiries` ADD CONSTRAINT `fk_inquiry_category` FOREIGN KEY (`inquiry_category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
