-- ═══════════════════════════════════════════════════════════════
-- elallasform.hu — Teljes adatbázis séma
-- Futtatás: mysql -u root -p elallasform < schema.sql
-- ═══════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET time_zone = '+02:00';

-- ── PLATFORM FELHASZNÁLÓK ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255)  NOT NULL,
    password_hash VARCHAR(255)  NOT NULL,
    verify_token  VARCHAR(128)  DEFAULT NULL,
    verified_at   DATETIME      DEFAULT NULL,
    created_at    DATETIME      DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_email (email),
    KEY idx_verify_token (verify_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── WEBSHOPOK (céges adatok + widget token) ──────────────────────
CREATE TABLE IF NOT EXISTS shops (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    company_name    VARCHAR(255) NOT NULL,
    tax_number      VARCHAR(50)  DEFAULT NULL,
    address         VARCHAR(500) DEFAULT NULL,
    contact_email   VARCHAR(255) NOT NULL,
    website         VARCHAR(255) DEFAULT NULL,
    widget_token    VARCHAR(128) NOT NULL,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_widget_token (widget_token),
    KEY idx_user_id (user_id),
    CONSTRAINT fk_shops_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ELÁLLÁSI KÉRELMEK ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS withdrawals (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id             INT UNSIGNED NOT NULL,

    -- Vásárló adatok (kötelező mezők a jogszabály alapján)
    consumer_name       VARCHAR(255) NOT NULL,
    consumer_email      VARCHAR(255) NOT NULL,
    order_id            VARCHAR(255) NOT NULL,

    -- Azonosítók
    ref_number          VARCHAR(32)  NOT NULL,          -- pl. EF-20260619-AB12CD
    confirmation_token  VARCHAR(128) NOT NULL,          -- e-mail visszaigazolás linkhez (opcionális flow)

    -- Státusz és időbélyegek (törvény: megküldés napja és időpontja kötelező)
    status              ENUM('pending','confirmed') NOT NULL DEFAULT 'pending',
    submitted_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    confirmed_at        DATETIME     DEFAULT NULL,

    -- Technikai mezők
    ip_address          VARCHAR(45)  DEFAULT NULL,      -- GDPR: csak szükség esetén
    user_agent          VARCHAR(500) DEFAULT NULL,

    UNIQUE KEY uq_ref_number         (ref_number),
    UNIQUE KEY uq_confirmation_token (confirmation_token),
    KEY idx_shop_id      (shop_id),
    KEY idx_consumer_email(consumer_email),
    KEY idx_order_id     (order_id),
    KEY idx_status       (status),
    KEY idx_submitted_at (submitted_at),

    CONSTRAINT fk_withdrawals_shop FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── RATE LIMITING ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rate_limits (
    `key`       VARCHAR(128) NOT NULL PRIMARY KEY,
    hits        INT UNSIGNED NOT NULL DEFAULT 1,
    expires_at  DATETIME     NOT NULL,

    KEY idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── E-MAIL NAPLÓ (opcionális, auditáláshoz) ───────────────────────
CREATE TABLE IF NOT EXISTS email_log (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    withdrawal_id INT UNSIGNED DEFAULT NULL,
    recipient     VARCHAR(255) NOT NULL,
    type          ENUM('consumer_receipt','shop_notification','verification') NOT NULL,
    status        ENUM('sent','failed') NOT NULL DEFAULT 'sent',
    sent_at       DATETIME DEFAULT CURRENT_TIMESTAMP,

    KEY idx_withdrawal_id (withdrawal_id),
    CONSTRAINT fk_email_log_withdrawal FOREIGN KEY (withdrawal_id)
        REFERENCES withdrawals(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── HASZNOS NÉZETEK ──────────────────────────────────────────────

-- Legutóbbi 30 nap elállásai shopok szerint
CREATE OR REPLACE VIEW v_recent_withdrawals AS
SELECT
    w.*,
    s.company_name,
    s.contact_email AS shop_email
FROM withdrawals w
JOIN shops s ON w.shop_id = s.id
WHERE w.submitted_at >= NOW() - INTERVAL 30 DAY
ORDER BY w.submitted_at DESC;

-- Shop statisztikák
CREATE OR REPLACE VIEW v_shop_stats AS
SELECT
    s.id               AS shop_id,
    s.company_name,
    COUNT(w.id)        AS total_withdrawals,
    SUM(w.submitted_at >= NOW() - INTERVAL 24 HOUR) AS today,
    SUM(w.status = 'pending')                        AS pending,
    MAX(w.submitted_at)                              AS last_withdrawal
FROM shops s
LEFT JOIN withdrawals w ON w.shop_id = s.id
GROUP BY s.id, s.company_name;