-- ============================================================
-- Migracja 003: Media + Realizacje + Zapytania
-- Tartak Dana CMS — Etap 2
-- Kompatybilność: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------
-- Tabela: media
-- Zdjęcia powiązane z encjami (product/realization/page).
-- Warianty (lg/md/sm/thumb) wyznaczane konwencją nazw.
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `media` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `entity_type`   ENUM('product','realization','category','page') NOT NULL,
    `entity_id`     INT UNSIGNED    NOT NULL,
    `file_basename` VARCHAR(100)    NOT NULL COMMENT 'np. szalowka-a1b2c3d4',
    `ext`           VARCHAR(10)     NOT NULL DEFAULT 'jpg' COMMENT 'Oryginalne rozszerzenie',
    `width`         INT UNSIGNED    NOT NULL DEFAULT 0,
    `height`        INT UNSIGNED    NOT NULL DEFAULT 0,
    `file_size`     INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT 'Bytes (oryginał)',
    `alt_text`      VARCHAR(255)    NOT NULL DEFAULT '',
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_entity` (`entity_type`, `entity_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: realizations
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `realizations` (
    `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,
    `location_text`    VARCHAR(200)    NOT NULL DEFAULT '',
    `meta_title`       VARCHAR(70)     NOT NULL DEFAULT '',
    `meta_description` VARCHAR(160)    NOT NULL DEFAULT '',
    `is_published`     TINYINT(1)      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_realizations_slug` (`slug`),
    INDEX `idx_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: realization_products (many-to-many)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `realization_products` (
    `realization_id` INT UNSIGNED NOT NULL,
    `product_id`     INT UNSIGNED NOT NULL,
    PRIMARY KEY (`realization_id`, `product_id`),
    CONSTRAINT `fk_rp_realization` FOREIGN KEY (`realization_id`)
        REFERENCES `realizations`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_rp_product` FOREIGN KEY (`product_id`)
        REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: inquiries (zapytania z formularzy)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inquiries` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `source`          ENUM('contact','product','realization','quote') NOT NULL DEFAULT 'contact',
    `product_id`      INT UNSIGNED    DEFAULT NULL,
    `realization_id`  INT UNSIGNED    DEFAULT NULL,
    `name`            VARCHAR(100)    NOT NULL,
    `email`           VARCHAR(255)    NOT NULL,
    `phone`           VARCHAR(30)     NOT NULL DEFAULT '',
    `quantity_value`  VARCHAR(20)     DEFAULT NULL,
    `quantity_unit`   VARCHAR(10)     DEFAULT NULL,
    `message`         TEXT            NULL,
    `rodo_consent`    TINYINT(1)      NOT NULL DEFAULT 0,
    `status`          ENUM('new','read','replied','closed') NOT NULL DEFAULT 'new',
    `ip_hash`         VARCHAR(64)     NOT NULL DEFAULT '' COMMENT 'SHA256 IP (rate limiting + analityka)',
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_source` (`source`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_product` (`product_id`),
    INDEX `idx_ip_time` (`ip_hash`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Ustawienia SMTP (dodaj do istniejącej tabeli settings)
-- -----------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('smtp_enabled',  '0'),
    ('smtp_host',     ''),
    ('smtp_port',     '465'),
    ('smtp_username', ''),
    ('smtp_password', ''),
    ('smtp_encryption', 'ssl'),
    ('smtp_from_email', ''),
    ('smtp_from_name',  'Tartak Dana')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;
