USE bulo;

CREATE TABLE IF NOT EXISTS tarifa_regioes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  ordem INT NOT NULL DEFAULT 100,
  geocerca_json LONGTEXT NOT NULL,
  centro_lat DECIMAL(10,7) NULL,
  centro_lng DECIMAL(10,7) NULL,
  preco_base DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  preco_por_km DECIMAL(10,2) NOT NULL DEFAULT 2.50,
  preco_por_minuto DECIMAL(10,2) NOT NULL DEFAULT 0.35,
  taxa_servico DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  plus_preco_base DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  plus_preco_por_km DECIMAL(10,2) NOT NULL DEFAULT 2.50,
  plus_preco_por_minuto DECIMAL(10,2) NOT NULL DEFAULT 0.35,
  plus_taxa_servico DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  plus_valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  pro_preco_base DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  pro_preco_por_km DECIMAL(10,2) NOT NULL DEFAULT 2.50,
  pro_preco_por_minuto DECIMAL(10,2) NOT NULL DEFAULT 0.35,
  pro_taxa_servico DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  pro_valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00,
  economica_ativo TINYINT(1) NOT NULL DEFAULT 1,
  plus_ativo TINYINT(1) NOT NULL DEFAULT 1,
  pro_ativo TINYINT(1) NOT NULL DEFAULT 1,
  criado_por VARCHAR(80) NULL,
  atualizado_por VARCHAR(80) NULL,
  criado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tarifa_regioes_ativo_ordem (ativo, ordem),
  INDEX idx_tarifa_regioes_centro (centro_lat, centro_lng)
);

SET @col_valor_minimo := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'valor_minimo'
);
SET @sql := IF(@col_valor_minimo = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00 AFTER taxa_servico', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_plus_preco_base := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'plus_preco_base'
);
SET @sql := IF(@col_plus_preco_base = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN plus_preco_base DECIMAL(10,2) NOT NULL DEFAULT 5.00 AFTER taxa_servico', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_plus_preco_por_km := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'plus_preco_por_km'
);
SET @sql := IF(@col_plus_preco_por_km = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN plus_preco_por_km DECIMAL(10,2) NOT NULL DEFAULT 2.50 AFTER plus_preco_base', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_plus_preco_por_minuto := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'plus_preco_por_minuto'
);
SET @sql := IF(@col_plus_preco_por_minuto = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN plus_preco_por_minuto DECIMAL(10,2) NOT NULL DEFAULT 0.35 AFTER plus_preco_por_km', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_plus_taxa_servico := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'plus_taxa_servico'
);
SET @sql := IF(@col_plus_taxa_servico = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN plus_taxa_servico DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER plus_preco_por_minuto', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_plus_valor_minimo := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'plus_valor_minimo'
);
SET @sql := IF(@col_plus_valor_minimo = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN plus_valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00 AFTER plus_taxa_servico', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_pro_preco_base := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'pro_preco_base'
);
SET @sql := IF(@col_pro_preco_base = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN pro_preco_base DECIMAL(10,2) NOT NULL DEFAULT 5.00 AFTER plus_taxa_servico', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_pro_preco_por_km := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'pro_preco_por_km'
);
SET @sql := IF(@col_pro_preco_por_km = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN pro_preco_por_km DECIMAL(10,2) NOT NULL DEFAULT 2.50 AFTER pro_preco_base', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_pro_preco_por_minuto := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'pro_preco_por_minuto'
);
SET @sql := IF(@col_pro_preco_por_minuto = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN pro_preco_por_minuto DECIMAL(10,2) NOT NULL DEFAULT 0.35 AFTER pro_preco_por_km', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_pro_taxa_servico := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'pro_taxa_servico'
);
SET @sql := IF(@col_pro_taxa_servico = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN pro_taxa_servico DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER pro_preco_por_minuto', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_pro_valor_minimo := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tarifa_regioes' AND COLUMN_NAME = 'pro_valor_minimo'
);
SET @sql := IF(@col_pro_valor_minimo = 0, 'ALTER TABLE tarifa_regioes ADD COLUMN pro_valor_minimo DECIMAL(10,2) NOT NULL DEFAULT 5.00 AFTER pro_taxa_servico', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_tarifa_regiao := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'corridas' AND COLUMN_NAME = 'id_tarifa_regiao'
);
SET @sql := IF(@col_tarifa_regiao = 0, 'ALTER TABLE corridas ADD COLUMN id_tarifa_regiao INT NULL AFTER categoria_veiculo', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_tarifa_regiao := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'corridas' AND INDEX_NAME = 'idx_corridas_tarifa_regiao'
);
SET @sql := IF(@idx_tarifa_regiao = 0, 'ALTER TABLE corridas ADD INDEX idx_corridas_tarifa_regiao (id_tarifa_regiao)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
