CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pumps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  pump_type VARCHAR(80) NOT NULL DEFAULT 'Multiproducto',
  faces INT NOT NULL DEFAULT 2,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  review_type ENUM('surtidores','generador','lavado') NOT NULL,
  review_date DATE NOT NULL,
  responsible_name VARCHAR(160) NOT NULL,
  responsible_email VARCHAR(160) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'guardado',
  data_json LONGTEXT NOT NULL,
  signature_image VARCHAR(255) NULL,
  pdf_path VARCHAR(255) NULL,
  email_sent TINYINT(1) NOT NULL DEFAULT 0,
  email_error TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_type_date (review_type, review_date),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS review_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  review_id INT NOT NULL,
  photo_type VARCHAR(80) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_review_photos_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings(setting_key, setting_value) VALUES
('station_name', 'Copec Ogana 20830'),
('emails', '["20830@ESCOPEC.CL"]')
ON DUPLICATE KEY UPDATE setting_value = setting_value;
