USE bulo;

SET @col_banco_nome := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_nome'
);
SET @sql := IF(@col_banco_nome = 0, 'ALTER TABLE usuarios ADD COLUMN banco_nome VARCHAR(80) NULL AFTER localizacao_atualizada_em', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_banco_agencia := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_agencia'
);
SET @sql := IF(@col_banco_agencia = 0, 'ALTER TABLE usuarios ADD COLUMN banco_agencia VARCHAR(20) NULL AFTER banco_nome', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_banco_conta := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_conta'
);
SET @sql := IF(@col_banco_conta = 0, 'ALTER TABLE usuarios ADD COLUMN banco_conta VARCHAR(30) NULL AFTER banco_agencia', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_banco_tipo := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_tipo_conta'
);
SET @sql := IF(@col_banco_tipo = 0, 'ALTER TABLE usuarios ADD COLUMN banco_tipo_conta ENUM(''corrente'', ''poupanca'', ''pagamento'') NULL AFTER banco_conta', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_titular_nome := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_titular_nome'
);
SET @sql := IF(@col_titular_nome = 0, 'ALTER TABLE usuarios ADD COLUMN banco_titular_nome VARCHAR(120) NULL AFTER banco_tipo_conta', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_titular_doc := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'banco_titular_documento'
);
SET @sql := IF(@col_titular_doc = 0, 'ALTER TABLE usuarios ADD COLUMN banco_titular_documento VARCHAR(20) NULL AFTER banco_titular_nome', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_pix_tipo := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'pix_tipo'
);
SET @sql := IF(@col_pix_tipo = 0, 'ALTER TABLE usuarios ADD COLUMN pix_tipo ENUM(''cpf'', ''cnpj'', ''email'', ''celular'', ''aleatoria'') NULL AFTER banco_titular_documento', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_pix_chave := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'usuarios' AND COLUMN_NAME = 'pix_chave'
);
SET @sql := IF(@col_pix_chave = 0, 'ALTER TABLE usuarios ADD COLUMN pix_chave VARCHAR(120) NULL AFTER pix_tipo', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS indicacao_retiradas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_usuario INT NOT NULL,
  valor DECIMAL(10,2) NOT NULL,
  status ENUM('pendente', 'em_processamento', 'paga', 'reprovada', 'cancelada') NOT NULL DEFAULT 'pendente',
  pix_tipo ENUM('cpf', 'cnpj', 'email', 'celular', 'aleatoria') NULL,
  pix_chave VARCHAR(120) NULL,
  banco_nome VARCHAR(80) NULL,
  banco_agencia VARCHAR(20) NULL,
  banco_conta VARCHAR(30) NULL,
  banco_tipo_conta ENUM('corrente', 'poupanca', 'pagamento') NULL,
  banco_titular_nome VARCHAR(120) NULL,
  banco_titular_documento VARCHAR(20) NULL,
  prazo_limite DATE NULL,
  observacao VARCHAR(255) NULL,
  solicitado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processado_por VARCHAR(80) NULL,
  processado_em DATETIME NULL,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_indicacao_retiradas_usuario FOREIGN KEY (id_usuario) REFERENCES usuarios(id),
  INDEX idx_indicacao_retiradas_usuario (id_usuario),
  INDEX idx_indicacao_retiradas_status (status),
  INDEX idx_indicacao_retiradas_solicitado (solicitado_em)
);
