CREATE DATABASE IF NOT EXISTS molarcity_shuttle CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE molarcity_shuttle;
CREATE TABLE IF NOT EXISTS clinicas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(255) NOT NULL,
  usuario VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email VARCHAR(150),
  telefono VARCHAR(50),
  direccion_text VARCHAR(500),
  lat DECIMAL(10,7),
  lng DECIMAL(10,7),
  google_maps_url VARCHAR(500),
  timezone VARCHAR(64) DEFAULT 'America/Phoenix',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active TINYINT(1) DEFAULT 1,
  notas TEXT
);
CREATE TABLE IF NOT EXISTS operadores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(255) NOT NULL,
  email VARCHAR(150) UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','reportes','gestor_clinicas','chofer') DEFAULT 'chofer',
  telefono VARCHAR(50),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS tarifas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  clavelugar VARCHAR(100) NOT NULL UNIQUE,
  nombreciudad VARCHAR(150) NOT NULL,
  nombreestado VARCHAR(150) NOT NULL,
  costo DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO tarifas (clavelugar, nombreciudad, nombreestado, costo) VALUES ('YUMA_AZ','Yuma','Arizona',50.00);
CREATE TABLE IF NOT EXISTS servicios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  clinica_id INT NOT NULL,
  operador_id INT DEFAULT NULL,
  fecha_servicio DATETIME NOT NULL,
  paciente_nombre VARCHAR(255) NOT NULL,
  paciente_telefono VARCHAR(50),
  paciente_email VARCHAR(150),
  pickup_address VARCHAR(255),
  pickup_lat DECIMAL(10,7),
  pickup_lng DECIMAL(10,7),
  direccion_destino VARCHAR(255),
  lat_destino DECIMAL(10,7),
  lng_destino DECIMAL(10,7),
  costo_base DECIMAL(10,2) DEFAULT 0,
  costo_total DECIMAL(10,2) DEFAULT 0,
  notas TEXT,
  estatus ENUM('pendiente','en_proceso','completado','cancelado') DEFAULT 'pendiente',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (clinica_id) REFERENCES clinicas(id),
  FOREIGN KEY (operador_id) REFERENCES operadores(id)
);
