-- ============================================================
--  MooChedda crypto billing migration
--  Adds billing_crypto_sessions table and extends billing_invoices
--  to track crypto payments in payment history.
--  Run: node -e "require('./models/db').execute(require('fs').readFileSync('./database/migration_moochedda.sql','utf8'))"
-- ============================================================

CREATE TABLE IF NOT EXISTS billing_crypto_sessions (
    id              INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    invoice_id      VARCHAR(255)   NOT NULL UNIQUE,
    user_count      INT            NOT NULL,
    duration_months INT            NOT NULL,
    amount          DECIMAL(10,2)  NOT NULL,
    currency        VARCHAR(8)     NOT NULL,
    tenant_id       INT UNSIGNED   NOT NULL,
    created_by      INT UNSIGNED   NULL,
    status          ENUM('pending','paid','expired') NOT NULL DEFAULT 'pending',
    paid_at         DATETIME       NULL,
    created_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant  (tenant_id),
    INDEX idx_invoice (invoice_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extend billing_invoices to store crypto metadata
ALTER TABLE billing_invoices
    ADD COLUMN IF NOT EXISTS crypto_invoice_id VARCHAR(255) NULL AFTER stripe_payment_intent_id,
    ADD COLUMN IF NOT EXISTS crypto_currency    VARCHAR(8)  NULL AFTER crypto_invoice_id;
