-- MooChedda crypto payment invoices for GRC subscription billing
-- $1.00 = 1 B2B or 1 USDC; amount mirrors the USD dollar price

CREATE TABLE IF NOT EXISTS moochedda_invoices (
  id               CHAR(36)           NOT NULL DEFAULT (UUID()),
  tenant_id        CHAR(36)           NOT NULL,
  invoice_id       VARCHAR(255)       DEFAULT NULL,
  user_id          CHAR(36)           DEFAULT NULL,
  amount           DECIMAL(20,6)      NOT NULL,
  currency         VARCHAR(8)         NOT NULL DEFAULT 'USDC',
  months           SMALLINT UNSIGNED  NOT NULL DEFAULT 1,
  plan_key         VARCHAR(64)        DEFAULT NULL,
  label            VARCHAR(500)       NOT NULL,
  payment_url      VARCHAR(1000)      DEFAULT NULL,
  status           ENUM('pending','paid','expired','cancelled') NOT NULL DEFAULT 'pending',
  memo             VARCHAR(500)       DEFAULT NULL,
  tx_hash          VARCHAR(255)       DEFAULT NULL,
  paid_at          DATETIME           DEFAULT NULL,
  paid_until_after DATETIME           DEFAULT NULL,
  expires_at       DATETIME           DEFAULT NULL,
  created_at       DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_mc_tenant  (tenant_id),
  INDEX idx_mc_status  (status),
  INDEX idx_mc_invoice (invoice_id),
  CONSTRAINT fk_mc_invoices_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_mc_invoices_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
