-- ============================================================
-- Migracja 008: Etap 5 - PDF kart technicznych, reset hasla,
--                log audytu, SEO globalne
-- Tartak Dana CMS
-- Kompatybilnosc: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------
-- Produkty: karta techniczna PDF (pojedynczy plik)
-- -----------------------------------------------------------
SET @has_technical_pdf_path := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'technical_pdf_path'
);
SET @sql := IF(
    @has_technical_pdf_path = 0,
    'ALTER TABLE `products` ADD COLUMN `technical_pdf_path` VARCHAR(255) NULL AFTER `main_image_path`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_technical_pdf_name := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'technical_pdf_name'
);
SET @sql := IF(
    @has_technical_pdf_name = 0,
    'ALTER TABLE `products` ADD COLUMN `technical_pdf_name` VARCHAR(190) NOT NULL DEFAULT \'\' AFTER `technical_pdf_path`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_technical_pdf_size := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'technical_pdf_size'
);
SET @sql := IF(
    @has_technical_pdf_size = 0,
    'ALTER TABLE `products` ADD COLUMN `technical_pdf_size` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `technical_pdf_name`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_technical_pdf_updated_at := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'technical_pdf_updated_at'
);
SET @sql := IF(
    @has_technical_pdf_updated_at = 0,
    'ALTER TABLE `products` ADD COLUMN `technical_pdf_updated_at` DATETIME NULL AFTER `technical_pdf_size`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------
-- Tokeny resetu hasla (jednorazowe, z TTL)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED    NOT NULL,
    `token_hash`  CHAR(64)        NOT NULL,
    `ip_address`  VARCHAR(45)     NOT NULL DEFAULT '',
    `user_agent`  VARCHAR(255)    NOT NULL DEFAULT '',
    `expires_at`  DATETIME        NOT NULL,
    `used_at`     DATETIME        NULL,
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_password_reset_token_hash` (`token_hash`),
    INDEX `idx_password_reset_user` (`user_id`),
    INDEX `idx_password_reset_expires` (`expires_at`),
    CONSTRAINT `fk_password_reset_user` FOREIGN KEY (`user_id`)
        REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Log audytu panelu administracyjnego
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`      INT UNSIGNED    NULL,
    `action`       VARCHAR(80)     NOT NULL,
    `entity_type`  VARCHAR(50)     NOT NULL DEFAULT '',
    `entity_id`    INT UNSIGNED    NULL,
    `details_json` TEXT            NULL,
    `ip_address`   VARCHAR(45)     NOT NULL DEFAULT '',
    `user_agent`   VARCHAR(255)    NOT NULL DEFAULT '',
    `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_audit_created` (`created_at`),
    INDEX `idx_audit_action_created` (`action`, `created_at`),
    INDEX `idx_audit_user_created` (`user_id`, `created_at`),
    CONSTRAINT `fk_audit_logs_user` FOREIGN KEY (`user_id`)
        REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Ustawienia SEO globalne
-- -----------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('seo_robots_default', 'index, follow'),
    ('seo_ga4_id', ''),
    ('seo_gsc_verification', ''),
    ('seo_title_suffix', ' | Tartak Dana'),
    ('seo_description_fallback', '')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

