-- ===========================================================================
--  Cappuccino - local website store (SQLite).
--  Auto-loaded by src/Core/Database.php the first time storage/site.db is
--  missing. Holds only website-owned content; all game data stays in MySQL.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS settings (
    key        TEXT PRIMARY KEY,
    value      TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT OR IGNORE INTO settings (key, value) VALUES
    ('site_online',      '1'),
    ('download_client',  ''),
    ('download_mirrors', ''),
    ('download_requirements', ''),
    ('discord_url',      ''),
    ('discord_widget_id', ''),          -- Discord server ID -> right-side live widget
    ('facebook_url',     ''),
    ('spawn_shinsoo',    '1,958870,272105'),    -- unstuck target: mapIndex,x,y
    ('spawn_chunjo',     '21,1140088,292628'),
    ('spawn_jinno',      '41,1327064,253957'),
    ('referral_min_level',    '15'),
    ('referral_min_playtime', '15'),            -- minutes
    ('referral_reward_coins', '0'),
    ('logo_image',       ''),          -- header banner logo (URL); empty = text
    ('banner_image',     ''),          -- header banner background (URL)
    ('open_server_at',   ''),          -- e.g. 2026-09-01 20:00 -> shows a countdown
    ('hero_kicker',      'Enter the East'),
    ('hero_description', 'A fresh Metin2 world - balanced rates, active staff, weekly events.');

-- --- News ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS news (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    slug         TEXT NOT NULL UNIQUE,
    title        TEXT NOT NULL,
    category     TEXT NOT NULL DEFAULT 'news',
    excerpt      TEXT NOT NULL DEFAULT '',
    body         TEXT NOT NULL,
    cover_image  TEXT NOT NULL DEFAULT '',
    author_id    INTEGER NOT NULL DEFAULT 0,
    author_name  TEXT NOT NULL DEFAULT '',
    is_published INTEGER NOT NULL DEFAULT 1,
    is_pinned    INTEGER NOT NULL DEFAULT 0,
    published_at TEXT NOT NULL DEFAULT (datetime('now')),
    created_at   TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_news_pub ON news (is_published, published_at);
CREATE INDEX IF NOT EXISTS idx_news_cat ON news (category);

-- --- Item shop catalog -------------------------------------------------
-- Canonical catalog lives here and is managed from the CMS admin panel.
-- When the game MySQL is reachable each row is mirrored into player.ishop_data
-- (id kept in sync) so the in-game item shop stays in agreement.
CREATE TABLE IF NOT EXISTS shop_items (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    name             TEXT NOT NULL,
    description      TEXT NOT NULL DEFAULT '',
    image            TEXT NOT NULL DEFAULT '',
    category_type    INTEGER NOT NULL DEFAULT 0,
    category_subtype INTEGER NOT NULL DEFAULT 0,
    item_vnum        INTEGER NOT NULL,
    item_count       INTEGER NOT NULL DEFAULT 1,
    price            INTEGER NOT NULL DEFAULT 0,   -- in coins
    discount         INTEGER NOT NULL DEFAULT 0,   -- 0..100 %
    stock            INTEGER,                      -- NULL = unlimited
    week_limit       INTEGER NOT NULL DEFAULT 0,
    month_limit      INTEGER NOT NULL DEFAULT 0,
    featured         INTEGER NOT NULL DEFAULT 0,
    sort_order       INTEGER NOT NULL DEFAULT 0,
    is_active        INTEGER NOT NULL DEFAULT 1,
    sell_count       INTEGER NOT NULL DEFAULT 0,
    created_at       TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at       TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_shop_items_cat ON shop_items (category_type, category_subtype, is_active);

CREATE TABLE IF NOT EXISTS itemshop_categories (
    category_type    INTEGER NOT NULL,
    category_subtype INTEGER NOT NULL,
    display_name     TEXT NOT NULL,
    icon             TEXT NOT NULL DEFAULT '',
    sort_order       INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (category_type, category_subtype)
);

-- --- In-game event presentation overlay (player.event_table is read live) --
CREATE TABLE IF NOT EXISTS event_meta (
    event_index  TEXT PRIMARY KEY,
    display_name TEXT NOT NULL DEFAULT '',
    icon         TEXT NOT NULL DEFAULT '',
    description  TEXT NOT NULL DEFAULT ''
);

-- --- Coin packages (real-money bundles, managed from the shop admin) ----
CREATE TABLE IF NOT EXISTS coin_packages (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    label       TEXT NOT NULL,
    coins       INTEGER NOT NULL DEFAULT 0,     -- base coins / MD
    bonus       INTEGER NOT NULL DEFAULT 0,     -- extra coins on top
    price       REAL NOT NULL DEFAULT 0,
    currency    TEXT NOT NULL DEFAULT 'EUR',
    processors  TEXT NOT NULL DEFAULT 'paypal,stripe',  -- csv: paypal | stripe
    is_featured INTEGER NOT NULL DEFAULT 0,
    is_active   INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

-- --- Coin top-ups (real-money) --------------------------------------
-- Authoritative local ledger. When MySQL is reachable the same rows are
-- mirrored into log.itemshop_payments for the game-side admin tools.
CREATE TABLE IF NOT EXISTS payments (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    provider       TEXT NOT NULL,               -- paypal | stripe
    transaction_id TEXT NOT NULL,
    account_id     INTEGER NOT NULL DEFAULT 0,
    login          TEXT NOT NULL DEFAULT '',
    package_id     TEXT NOT NULL DEFAULT '',
    amount         REAL NOT NULL DEFAULT 0,
    currency       TEXT NOT NULL DEFAULT 'EUR',
    coins          INTEGER NOT NULL DEFAULT 0,
    status         TEXT NOT NULL DEFAULT 'pending',  -- pending | completed | failed
    ip_address     TEXT NOT NULL DEFAULT '',
    extra_data     TEXT,
    created_at     TEXT NOT NULL DEFAULT (datetime('now')),
    completed_at   TEXT,
    UNIQUE (provider, transaction_id)
);
CREATE INDEX IF NOT EXISTS idx_payments_acc ON payments (account_id, created_at);

-- --- Item-shop coupons (discount codes applied at checkout) -----------
CREATE TABLE IF NOT EXISTS shop_coupons (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    code        TEXT NOT NULL UNIQUE,
    kind        TEXT NOT NULL DEFAULT 'percent',   -- percent | flat
    value       INTEGER NOT NULL DEFAULT 0,        -- percent 1..100  OR  flat coins
    min_price   INTEGER NOT NULL DEFAULT 0,        -- min product price to qualify
    max_uses    INTEGER NOT NULL DEFAULT 0,        -- 0 = unlimited
    used        INTEGER NOT NULL DEFAULT 0,
    per_account INTEGER NOT NULL DEFAULT 1,
    expires_at  TEXT,
    is_active   INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS shop_coupon_uses (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    coupon_id  INTEGER NOT NULL,
    code       TEXT NOT NULL DEFAULT '',
    account_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL DEFAULT 0,
    discount   INTEGER NOT NULL DEFAULT 0,
    used_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_coupon_uses_acc ON shop_coupon_uses (account_id, coupon_id);

-- --- Redeem / gift codes ----------------------------------------------
CREATE TABLE IF NOT EXISTS redeem_codes (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    code         TEXT NOT NULL UNIQUE,
    reward_type  TEXT NOT NULL DEFAULT 'coins',   -- coins | jcoins | item
    reward_value INTEGER NOT NULL DEFAULT 0,      -- coin amount OR item vnum
    reward_count INTEGER NOT NULL DEFAULT 1,
    max_uses     INTEGER NOT NULL DEFAULT 1,      -- 0 = unlimited
    used_count   INTEGER NOT NULL DEFAULT 0,
    per_account  INTEGER NOT NULL DEFAULT 1,
    expires_at   TEXT,
    is_active    INTEGER NOT NULL DEFAULT 1,
    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS redeem_uses (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    code_id    INTEGER NOT NULL,
    account_id INTEGER NOT NULL,
    ip_address TEXT NOT NULL DEFAULT '',
    used_at    TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (code_id, account_id)
);

-- --- Vote4Coins ------------------------------------------------------
CREATE TABLE IF NOT EXISTS vote_sites (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    name         TEXT NOT NULL,
    url          TEXT NOT NULL,
    icon         TEXT NOT NULL DEFAULT '',
    reward_coins INTEGER NOT NULL DEFAULT 10,
    cooldown_h   INTEGER NOT NULL DEFAULT 12,
    callback_key TEXT NOT NULL DEFAULT '',
    is_active    INTEGER NOT NULL DEFAULT 1,
    sort_order   INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS vote_log (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    site_id      INTEGER NOT NULL,
    account_id   INTEGER NOT NULL,
    ip_address   TEXT NOT NULL DEFAULT '',
    reward       INTEGER NOT NULL DEFAULT 0,
    status       TEXT NOT NULL DEFAULT 'pending', -- pending | confirmed
    voted_at     TEXT NOT NULL DEFAULT (datetime('now')),
    confirmed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_vote_log_acc ON vote_log (account_id, site_id, voted_at);

-- --- Referrals ---------------------------------------------------
CREATE TABLE IF NOT EXISTS referrals (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    referrer_id    INTEGER NOT NULL,
    referred_id    INTEGER NOT NULL UNIQUE,
    reward_given   INTEGER NOT NULL DEFAULT 0,
    rewarded_at    TEXT,
    referred_level INTEGER NOT NULL DEFAULT 0,
    is_valid       INTEGER NOT NULL DEFAULT 0,
    validated_at   TEXT,
    created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_ref_referrer ON referrals (referrer_id);

-- Per-account promoter code + click counter.
CREATE TABLE IF NOT EXISTS referral_codes (
    account_id INTEGER PRIMARY KEY,
    code       TEXT NOT NULL UNIQUE,
    clicks     INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- --- Password recovery ------------------------------------------
CREATE TABLE IF NOT EXISTS password_resets (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    ip_address TEXT NOT NULL DEFAULT '',
    expires_at TEXT NOT NULL,
    used_at    TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- --- Support tickets -----------------------------------------
CREATE TABLE IF NOT EXISTS tickets (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    login      TEXT NOT NULL DEFAULT '',
    subject    TEXT NOT NULL,
    category   TEXT NOT NULL DEFAULT 'general',   -- general | payment | bug | report | account
    status     TEXT NOT NULL DEFAULT 'open',      -- open | answered | closed
    priority   TEXT NOT NULL DEFAULT 'normal',    -- low | normal | high
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_tickets_acc ON tickets (account_id, status);
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets (status, updated_at);

CREATE TABLE IF NOT EXISTS ticket_messages (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket_id   INTEGER NOT NULL,
    author_type TEXT NOT NULL DEFAULT 'user',     -- user | staff
    author_id   INTEGER NOT NULL DEFAULT 0,
    author_name TEXT NOT NULL DEFAULT '',
    body        TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_ticket_msg ON ticket_messages (ticket_id, created_at);

-- --- Admin audit ----------------------------------------------
CREATE TABLE IF NOT EXISTS admin_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER NOT NULL,
    login      TEXT NOT NULL DEFAULT '',
    action     TEXT NOT NULL,
    target     TEXT NOT NULL DEFAULT '',
    detail     TEXT,
    ip_address TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- --- Rate limiting (login / register / recovery / redeem / vote / payment) ---
CREATE TABLE IF NOT EXISTS rate_limits (
    k        TEXT PRIMARY KEY,
    hits     INTEGER NOT NULL DEFAULT 0,
    reset_at INTEGER NOT NULL DEFAULT 0
);

-- --- Server monitor: auth / db / game channels / cores -------------------
CREATE TABLE IF NOT EXISTS monitor_targets (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    host       TEXT NOT NULL DEFAULT '127.0.0.1',
    port       INTEGER NOT NULL,
    kind       TEXT NOT NULL DEFAULT 'custom',   -- auth | db | game | channel | core | web | custom
    check_type TEXT NOT NULL DEFAULT 'tcp',      -- tcp | mysql | http  (how the probe verifies "alive")
    check_opt  TEXT NOT NULL DEFAULT '',         -- http: path to request; mysql: schema override
    is_enabled INTEGER NOT NULL DEFAULT 1,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (host, port, name)
);
CREATE TABLE IF NOT EXISTS monitor_status (
    target_id    INTEGER PRIMARY KEY,
    is_up        INTEGER NOT NULL DEFAULT 1,
    since        TEXT NOT NULL DEFAULT (datetime('now')),   -- when the current state began
    last_check   TEXT,
    fail_streak  INTEGER NOT NULL DEFAULT 0,
    ok_streak    INTEGER NOT NULL DEFAULT 0,
    latency_ms   INTEGER NOT NULL DEFAULT 0,
    checks       INTEGER NOT NULL DEFAULT 0,   -- total probes ever
    ups          INTEGER NOT NULL DEFAULT 0    -- probes that succeeded (for uptime %)
);
CREATE TABLE IF NOT EXISTS monitor_events (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    target_id  INTEGER NOT NULL,
    name       TEXT NOT NULL DEFAULT '',
    kind       TEXT NOT NULL DEFAULT '',
    event      TEXT NOT NULL,                 -- down | up
    latency_ms INTEGER NOT NULL DEFAULT 0,
    detail     TEXT NOT NULL DEFAULT '',
    downtime_s INTEGER,                       -- set on 'up' events: how long it was down
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_monitor_events ON monitor_events (target_id, created_at);