-- =====================================================================
-- Migration 014: Carteira de creditos + pacotes + recargas PIX (Asaas)
--
-- Modelo:
--   - companies tem credit_balance (creditos inteiros)
--   - operation_pricing define quanto cada (op_code, model) custa
--   - recharge_packages: pacotes pre-cadastrados pelo master (R$ -> creditos)
--   - recharges: cada cobranca PIX gerada (status pending/paid/expired/cancelled)
--   - credit_transactions: ledger append-only (todo +/- no saldo)
--   - user_tokens: tokens de auth do app RN (login email/senha)
--
-- Debit atomico:
--   UPDATE companies SET credit_balance = credit_balance - ?
--   WHERE id = ? AND credit_balance >= ?
--   (verifica affected_rows; se 0 = insuficiente)
-- =====================================================================

-- ---------------------------------------------------------------------
-- companies: campos de carteira
-- ---------------------------------------------------------------------
ALTER TABLE companies
  ADD COLUMN credit_balance INT NOT NULL DEFAULT 0
    COMMENT 'Saldo atual em creditos (debitado por uso, creditado por recarga)' AFTER price_per_seedkey,
  ADD COLUMN low_balance_credits INT NOT NULL DEFAULT 10
    COMMENT 'Limiar p/ aviso de saldo baixo no app' AFTER credit_balance,
  ADD COLUMN asaas_customer_id VARCHAR(64) NULL
    COMMENT 'ID do customer no Asaas (cus_xxx). Criado na 1a recarga.' AFTER low_balance_credits;

-- ---------------------------------------------------------------------
-- operation_pricing — custo em creditos por operacao+modelo
-- ---------------------------------------------------------------------
CREATE TABLE operation_pricing (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  op_code VARCHAR(40) NOT NULL COMMENT 'fw_update, library, workshop_login, seedkey, etc',
  tco_model VARCHAR(20) NOT NULL DEFAULT '*'
    COMMENT 'BVDR2/BVDR1/MTCO1390/PISTA_REDUZIDA ou * pra qualquer',
  credit_cost INT NOT NULL DEFAULT 1
    COMMENT 'Quantos creditos esta operacao consome',
  description VARCHAR(200) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_op_model (op_code, tco_model)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- recharge_packages — opcoes de recarga oferecidas no app
-- ---------------------------------------------------------------------
CREATE TABLE recharge_packages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(80) NOT NULL COMMENT 'ex: "Pacote Bronze"',
  price_brl DECIMAL(10,2) NOT NULL COMMENT 'Valor cobrado no PIX',
  credits INT NOT NULL COMMENT 'Creditos base do pacote',
  bonus_credits INT NOT NULL DEFAULT 0 COMMENT 'Bonus extra',
  highlight TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = destaque no app (ex "+vendido")',
  sort_order INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active_sort (active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- recharges — cada cobranca PIX
-- ---------------------------------------------------------------------
CREATE TABLE recharges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL COMMENT 'Quem gerou (NULL = master/web)',
  package_id INT UNSIGNED NULL COMMENT 'NULL se valor custom',
  price_brl DECIMAL(10,2) NOT NULL,
  credits_total INT NOT NULL COMMENT 'credits + bonus_credits congelados no momento',
  status ENUM('pending','paid','expired','cancelled','refunded') NOT NULL DEFAULT 'pending',
  provider VARCHAR(20) NOT NULL DEFAULT 'asaas',
  provider_payment_id VARCHAR(64) NULL COMMENT 'pay_xxx no Asaas',
  provider_customer_id VARCHAR(64) NULL,
  pix_qrcode_base64 MEDIUMTEXT NULL COMMENT 'PNG base64 do QR (Asaas devolve)',
  pix_copy_paste TEXT NULL COMMENT 'String copia-e-cola PIX',
  pix_expires_at DATETIME NULL,
  paid_at DATETIME NULL,
  webhook_received_at DATETIME NULL,
  raw_webhook JSON NULL COMMENT 'ultimo payload do webhook (auditoria)',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_payment (provider, provider_payment_id),
  INDEX idx_company_status (company_id, status, created_at),
  INDEX idx_status_created (status, created_at),
  CONSTRAINT fk_recharges_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_recharges_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_recharges_package FOREIGN KEY (package_id) REFERENCES recharge_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- credit_transactions — ledger append-only de TODA movimentacao de saldo
-- ---------------------------------------------------------------------
CREATE TABLE credit_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  delta INT NOT NULL COMMENT 'Positivo = credito, negativo = debito',
  balance_after INT NOT NULL COMMENT 'Saldo apos a transacao (snapshot)',
  kind ENUM('recharge','usage','refund','adjustment','bonus') NOT NULL,
  ref_table VARCHAR(40) NULL COMMENT 'recharges / seedkey_logs / usage_events / NULL',
  ref_id BIGINT UNSIGNED NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company_created (company_id, created_at),
  INDEX idx_ref (ref_table, ref_id),
  INDEX idx_kind_created (kind, created_at),
  CONSTRAINT fk_txn_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_txn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- user_tokens — auth do app RN (email/senha -> token bearer)
-- ---------------------------------------------------------------------
CREATE TABLE user_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token CHAR(64) NOT NULL UNIQUE COMMENT 'Hex 32B random',
  device_label VARCHAR(120) NULL COMMENT 'ex "Samsung Galaxy A52 - tecnico1"',
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL COMMENT 'NULL = nao expira',
  revoked_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id, revoked_at),
  CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SEED inicial — precos default + pacotes exemplo
-- ---------------------------------------------------------------------
INSERT INTO operation_pricing (op_code, tco_model, credit_cost, description) VALUES
  ('seedkey',           '*',         2, 'Geracao de seedkey (qualquer tacografo)'),
  ('fw_update',         'BVDR2',     5, 'Atualizacao de firmware BVDR2'),
  ('library',           'BVDR2',     2, 'Aplicacao de biblioteca OEM'),
  ('param_prog',        '*',         1, 'Programacao de parametro individual'),
  ('workshop_login',    'BVDR1',     1, 'Credenciais BVDR1 (codigo+senha)'),
  ('calibration',       '*',         3, 'Calibracao legal'),
  ('data_download',     '*',         1, 'Download de dados'),
  ('other',             '*',         1, 'Outras operacoes');

INSERT INTO recharge_packages (label, price_brl, credits, bonus_credits, highlight, sort_order) VALUES
  ('Pacote Bronze',  50.00,  100,   0, 0, 10),
  ('Pacote Prata',  150.00,  300,  50, 1, 20),
  ('Pacote Ouro',   400.00,  800, 200, 0, 30),
  ('Pacote Platina', 800.00, 1700, 500, 0, 40);

INSERT INTO settings (k, v) VALUES
  ('credits_enabled', '1'),
  ('credits_currency_label', 'creditos'),
  ('asaas_webhook_token_hash', '')
ON DUPLICATE KEY UPDATE v = VALUES(v);
