-- Banco: pesquisacorretor
CREATE TABLE IF NOT EXISTS agents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  creci VARCHAR(40) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  phone VARCHAR(30) NOT NULL,
  photo VARCHAR(255) NULL,
  state CHAR(2) NOT NULL,
  city VARCHAR(120) NOT NULL,
  region VARCHAR(20) NOT NULL,
  status ENUM('pending','active','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agent_id INT NOT NULL,
  gateway VARCHAR(20) NOT NULL DEFAULT 'mercadopago',
  payment_id VARCHAR(64) NULL,
  external_reference VARCHAR(64) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  qr_code TEXT NULL,
  qr_code_base64 MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_payments_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
  INDEX idx_external_ref (external_reference),
  INDEX idx_payment_id (payment_id)
);
