-- Migration: 0003_create_wallet_tables
-- Phase 4 (Wallet). Builds on the existing `credit_accounts` table
-- from Phase 3 — no changes to it are needed.

-- Purchasable credit plans. Prices/credits are admin-editable data,
-- never hardcoded in PHP, exactly like `ai_models`.
CREATE TABLE IF NOT EXISTS credit_plans (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(64)     NOT NULL,
    price_toman BIGINT UNSIGNED NOT NULL,
    credits     BIGINT UNSIGNED NOT NULL,
    is_active   TINYINT(1)      NOT NULL DEFAULT 1,
    sort_order  INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_credit_plans_active_sort (is_active, sort_order)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO credit_plans (title, price_toman, credits, sort_order) VALUES
    ('پلن ۵۰ هزار تومانی',  50000,  50000,  1),
    ('پلن ۱۰۰ هزار تومانی', 100000, 110000, 2),
    ('پلن ۲۰۰ هزار تومانی', 200000, 230000, 3),
    ('پلن ۵۰۰ هزار تومانی', 500000, 600000, 4);

-- One row per purchase attempt, from "create" through the gateway
-- callback's "verify" step. `trans_id` is Aghaye Pardakht's own
-- transaction identifier, used to correlate the callback with this row.
CREATE TABLE IF NOT EXISTS payment_transactions (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id        BIGINT UNSIGNED NOT NULL,
    credit_plan_id BIGINT UNSIGNED NOT NULL,
    trans_id       VARCHAR(64)     NULL,
    amount_toman   BIGINT UNSIGNED NOT NULL,
    credits        BIGINT UNSIGNED NOT NULL,
    status         ENUM('pending', 'paid', 'failed') NOT NULL DEFAULT 'pending',
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at        DATETIME        NULL,

    UNIQUE KEY uq_payment_transactions_trans_id (trans_id),
    KEY idx_payment_transactions_user (user_id),
    CONSTRAINT fk_payment_transactions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_payment_transactions_plan FOREIGN KEY (credit_plan_id) REFERENCES credit_plans (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Wallet purchases/usage also need their own conversation states on
-- the existing enum-backed column; no ALTER needed since
-- `conversation_state` is already a free-form VARCHAR — the new
-- value 'awaiting_plan_selection' is added at the PHP enum level
-- (App\Telegram\ConversationState) only.