-- Platform CRM schema (SQLite-compatible; MySQL: replace AUTOINCREMENT with AUTO_INCREMENT, TEXT dates ok)
-- Apply: sqlite3 storage/platform.sqlite < schema.sql

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------------
-- Catalogo bot (prodotti)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS products (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    slug            TEXT NOT NULL UNIQUE,          -- verify, shop-guard
    name            TEXT NOT NULL,
    tagline         TEXT NOT NULL DEFAULT '',
    description     TEXT NOT NULL DEFAULT '',
    telegram_username TEXT NOT NULL DEFAULT '',  -- senza @
    bot_token_enc   TEXT NOT NULL DEFAULT '',    -- cifrato in produzione
    kind            TEXT NOT NULL DEFAULT '',    -- tipo bot per il runtime: verify | shop | support (vuoto = deriva da code_path)
    code_path       TEXT NOT NULL DEFAULT '',    -- es. bots/verify-videonote
    webhook_path    TEXT NOT NULL DEFAULT '',    -- es. /hooks/bot/verify
    is_published    INTEGER NOT NULL DEFAULT 0,  -- 1 = visibile in catalogo
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------------
-- Piani per prodotto
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    code            TEXT NOT NULL UNIQUE,         -- verify_pro
    name            TEXT NOT NULL,                -- Pro
    interval        TEXT NOT NULL DEFAULT 'month', -- month | year | lifetime
    price_cents     INTEGER NOT NULL DEFAULT 0,   -- 1200 = 12.00 EUR
    currency        TEXT NOT NULL DEFAULT 'EUR',
    trial_days      INTEGER NOT NULL DEFAULT 0,
    max_groups      INTEGER NOT NULL DEFAULT 1,   -- 0 = illimitati
    features_json   TEXT NOT NULL DEFAULT '{}',   -- {"link_filter":true,"security":true}
    banner_text     TEXT NOT NULL DEFAULT '',     -- es. "-20%" o "Più venduto" mostrato sulla card
    banner_color    TEXT NOT NULL DEFAULT '',     -- colore hex del banner, es. #e8c547
    is_active       INTEGER NOT NULL DEFAULT 1,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_plans_product ON plans(product_id);

-- ---------------------------------------------------------------------------
-- Utenti piattaforma (login Telegram)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id         INTEGER NOT NULL UNIQUE,
    username            TEXT NOT NULL DEFAULT '',
    first_name          TEXT NOT NULL DEFAULT '',
    last_name           TEXT NOT NULL DEFAULT '',
    photo_url           TEXT NOT NULL DEFAULT '',
    language_code       TEXT NOT NULL DEFAULT 'it',
    is_platform_admin   INTEGER NOT NULL DEFAULT 0,
    created_at          TEXT NOT NULL DEFAULT (datetime('now')),
    last_login_at       TEXT
);

-- ---------------------------------------------------------------------------
-- Codici promo (sconti a tempo)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS promo_codes (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    code            TEXT NOT NULL UNIQUE,         -- ESTATE20 (uppercase)
    description     TEXT NOT NULL DEFAULT '',
    discount_type   TEXT NOT NULL DEFAULT 'percent', -- percent | fixed
    discount_value  INTEGER NOT NULL,             -- 20 = 20% oppure 500 = 5.00 EUR
    product_id      INTEGER REFERENCES products(id) ON DELETE SET NULL, -- NULL = tutti
    plan_id         INTEGER REFERENCES plans(id) ON DELETE SET NULL,    -- NULL = tutti i piani del product
    starts_at       TEXT NOT NULL,                -- ISO datetime
    ends_at         TEXT NOT NULL,
    max_uses        INTEGER NOT NULL DEFAULT 0,   -- 0 = illimitato
    uses_count      INTEGER NOT NULL DEFAULT 0,
    max_uses_per_user INTEGER NOT NULL DEFAULT 1,
    is_active       INTEGER NOT NULL DEFAULT 1,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_promos_code ON promo_codes(code);

-- ---------------------------------------------------------------------------
-- Ordini / pagamenti
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS orders (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id),
    plan_id         INTEGER NOT NULL REFERENCES plans(id),
    promo_code_id   INTEGER REFERENCES promo_codes(id),
    status          TEXT NOT NULL DEFAULT 'pending', -- pending|paid|failed|refunded
    amount_cents    INTEGER NOT NULL,               -- dopo sconto
    discount_cents  INTEGER NOT NULL DEFAULT 0,
    currency        TEXT NOT NULL DEFAULT 'EUR',
    provider        TEXT NOT NULL DEFAULT 'manual', -- manual|stripe|paypal|razorpay|demo|free
    provider_ref    TEXT NOT NULL DEFAULT '',       -- id sessione/pagamento del gateway
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    paid_at         TEXT
);

CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id);

-- ---------------------------------------------------------------------------
-- Abbonamenti (entitlement)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id),
    product_id      INTEGER NOT NULL REFERENCES products(id),
    plan_id         INTEGER NOT NULL REFERENCES plans(id),
    order_id        INTEGER REFERENCES orders(id),
    status          TEXT NOT NULL DEFAULT 'active', -- trial|active|past_due|canceled|expired
    starts_at       TEXT NOT NULL,
    ends_at         TEXT,                           -- NULL = lifetime
    cancel_at       TEXT,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_subs_user ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subs_product ON subscriptions(product_id, status);

-- Un utente: al massimo una sub "viva" per prodotto (enforce in app; opzionale unique parziale)
-- ---------------------------------------------------------------------------
-- Installazioni: bot X nel gruppo Y, di proprietà user Z
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bot_installations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    subscription_id INTEGER REFERENCES subscriptions(id) ON DELETE SET NULL,
    owner_user_id   INTEGER REFERENCES users(id),
    group_chat_id   INTEGER NOT NULL,
    group_title     TEXT NOT NULL DEFAULT '',
    is_active       INTEGER NOT NULL DEFAULT 1,
    installed_at    TEXT NOT NULL DEFAULT (datetime('now')),
    last_seen_at    TEXT,
    UNIQUE(product_id, group_chat_id)
);

CREATE INDEX IF NOT EXISTS idx_install_owner ON bot_installations(owner_user_id);
CREATE INDEX IF NOT EXISTS idx_install_group ON bot_installations(group_chat_id);

-- ---------------------------------------------------------------------------
-- Uso promo per utente (limite max_uses_per_user)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS promo_redemptions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    promo_code_id   INTEGER NOT NULL REFERENCES promo_codes(id),
    user_id         INTEGER NOT NULL REFERENCES users(id),
    order_id        INTEGER REFERENCES orders(id),
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(promo_code_id, user_id, order_id)
);

-- ---------------------------------------------------------------------------
-- Sessioni web semplici
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id              TEXT PRIMARY KEY,               -- random token
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at      TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------------
-- Audit leggero piattaforma
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER,
    type            TEXT NOT NULL,                  -- login, purchase, promo_applied, ...
    payload_json    TEXT NOT NULL DEFAULT '{}',
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------------
-- SEED: primo prodotto = bot verifica videobolla
-- ---------------------------------------------------------------------------
INSERT OR IGNORE INTO products (
    id, slug, name, tagline, description, telegram_username, kind, code_path, webhook_path, is_published, sort_order
) VALUES (
    1,
    'verify',
    'Verifica Videobolla',
    'Anti-spam con verifica in videobolla',
    'Mute i nuovi membri finché non completano la verifica. Sicurezza, filtro link, VIP, multi-gruppo.',
    '',
    'verify',
    'bots/verify-videonote',
    '/hooks/bot/verify',
    1,
    1
);

INSERT OR IGNORE INTO plans (id, product_id, code, name, interval, price_cents, currency, trial_days, max_groups, features_json, is_active, sort_order) VALUES
(1, 1, 'verify_free', 'Free', 'month', 0, 'EUR', 7, 1,
 '{"verification":true,"approval_manual":true,"blacklist_limit":20,"custom_messages":true,"leave_message":true,"rules":true}', 1, 1),
(2, 1, 'verify_pro', 'Pro', 'month', 1200, 'EUR', 7, 5,
 '{"verification":true,"approval_manual":true,"approval_auto":true,"approval_hybrid":true,"media":true,"link_filter":true,"security":true,"captcha":true,"vip":true,"warn":true,"repeat_messages":true,"polls":true,"banned_words":true,"leave_message":true,"gif_filter":true,"rules":true,"hierarchy":true,"slash_commands":true,"night_mode":true,"animated_icons":true,"blacklist_limit":0}', 1, 2),
(3, 1, 'verify_biz', 'Business', 'month', 3900, 'EUR', 0, 0,
 '{"verification":true,"approval_manual":true,"approval_auto":true,"approval_hybrid":true,"media":true,"link_filter":true,"security":true,"captcha":true,"vip":true,"warn":true,"repeat_messages":true,"polls":true,"banned_words":true,"leave_message":true,"gif_filter":true,"rules":true,"hierarchy":true,"slash_commands":true,"night_mode":true,"animated_icons":true,"blacklist_limit":0,"export":true,"backup":true,"webhook":true,"dashboard":true,"multi_group_unlimited":true,"media_privacy":true,"screenshot_guard":true,"group_history":true}', 1, 3);

-- Esempio promo (attiva 30 giorni dalla seed; aggiorna le date in admin)
INSERT OR IGNORE INTO promo_codes (
    id, code, description, discount_type, discount_value, product_id, plan_id,
    starts_at, ends_at, max_uses, uses_count, max_uses_per_user, is_active
) VALUES (
    1,
    'LANCIO20',
    'Sconto lancio -20% su Pro',
    'percent',
    20,
    1,
    2,
    datetime('now'),
    datetime('now', '+30 days'),
    100,
    0,
    1,
    1
);
