-- ============================================================
-- Tartak Dana CMS — schemat bazy danych
-- Wersja: 1.0 (Etap 0 — fundament)
-- Kompatybilność: MySQL 8.0 (OVH Perso)
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- -----------------------------------------------------------
-- Tabela: users
-- Role: super-admin | redaktor
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `email`         VARCHAR(255)    NOT NULL,
    `password_hash` VARCHAR(255)    NOT NULL,
    `name`          VARCHAR(100)    NOT NULL DEFAULT '',
    `role`          ENUM('super-admin','redaktor') NOT NULL DEFAULT 'redaktor',
    `is_active`     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_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: settings (klucz-wartość)
-- Jedno źródło prawdy dla danych firmy i ustawień globalnych.
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `id`             INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `setting_key`    VARCHAR(100)    NOT NULL,
    `setting_value`  TEXT            NULL,
    `updated_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Tabela: login_attempts (ochrona brute-force)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `ip_address`    VARCHAR(45)     NOT NULL,
    `email`         VARCHAR(255)    NOT NULL DEFAULT '',
    `attempted_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Dane początkowe: ustawienia firmy
-- -----------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('company_name_full',   'Tartak Dana Danuta Podlecka'),
    ('company_name_short',  'Tartak Dana'),
    ('company_street',      'ul. Michała Kajki 15'),
    ('company_postal_code', '19-300'),
    ('company_city',        'Straduny'),
    ('company_municipality','Ełk'),
    ('company_province',    'warmińsko-mazurskie'),
    ('company_nip',         ''),
    ('company_regon',       ''),
    ('company_phone',       '87 619 68 68'),
    ('company_phone_mobile','602 72 62 52'),
    ('company_email',       'biuro@tartakdana.pl'),
    ('company_logo_url',    ''),
    ('hours_weekdays_open', '7:00'),
    ('hours_weekdays_close','15:00'),
    ('hours_saturday_open', '7:00'),
    ('hours_saturday_close','12:00'),
    ('hours_sunday',        'Zamknięte'),
    ('gps_lat',             '53.8226'),
    ('gps_lng',             '22.3510'),
    ('google_maps_url',     'https://maps.google.com/?q=Tartak+Dana+Straduny'),
    ('google_business_url', '')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);
