-- Villas San Pancho TMK — esquema MySQL 8 / MariaDB 10.4+
SET NAMES utf8mb4;

CREATE TABLE villas (
  id TINYINT UNSIGNED PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  nombre VARCHAR(80) NOT NULL,
  capacidad SMALLINT UNSIGNED NULL,
  activa TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO villas (id, slug, nombre, capacidad) VALUES
  (1, 'villa-violeta', 'Villa Violeta', NULL),
  (2, 'villa-caleta',  'Villa Caleta',  NULL);

-- ---------- Usuarios ----------
CREATE TABLE usuarios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol ENUM('admin','gestor') NOT NULL DEFAULT 'gestor',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  intentos_fallidos TINYINT UNSIGNED NOT NULL DEFAULT 0,
  bloqueado_hasta DATETIME NULL,
  reset_token_hash CHAR(64) NULL,
  reset_expira DATETIME NULL,
  ultimo_acceso DATETIME NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Villas que puede gestionar un usuario con rol 'gestor' (admin ve todas)
CREATE TABLE usuario_villa (
  usuario_id INT UNSIGNED NOT NULL,
  villa_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (usuario_id, villa_id),
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
  FOREIGN KEY (villa_id) REFERENCES villas(id)
) ENGINE=InnoDB;

-- ---------- Reservaciones ----------
CREATE TABLE reservaciones (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  numero VARCHAR(20) NOT NULL UNIQUE,            -- VSP-2026-0001
  tipo ENUM('individual','conjunta') NOT NULL,   -- conjunta = ambas villas
  fecha_llegada DATE NOT NULL,
  fecha_salida DATE NOT NULL,                    -- noche de salida no se ocupa
  adultos SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  menores SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  huesped_nombre VARCHAR(150) NOT NULL,
  huesped_email VARCHAR(190) NULL,
  huesped_telefono VARCHAR(40) NULL,
  huesped_pais VARCHAR(60) NULL,
  idioma CHAR(2) NOT NULL DEFAULT 'es',
  mensaje TEXT NULL,
  estado ENUM('tentativa','confirmada','completada','cancelada') NOT NULL DEFAULT 'tentativa',
  origen ENUM('web','manual','airbnb','vrbo','otro') NOT NULL DEFAULT 'web',
  monto_acordado DECIMAL(12,2) NULL,
  moneda CHAR(3) NOT NULL DEFAULT 'USD',
  notas_internas TEXT NULL,
  confirmada_en DATETIME NULL,
  creado_por INT UNSIGNED NULL,                  -- NULL = desde el sitio web
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CHECK (fecha_salida > fecha_llegada),
  FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
  INDEX idx_res_fechas (fecha_llegada, fecha_salida),
  INDEX idx_res_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Villas que ocupa cada reservación (1 fila individual, 2 filas conjunta)
CREATE TABLE reservacion_villa (
  reservacion_id INT UNSIGNED NOT NULL,
  villa_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (reservacion_id, villa_id),
  FOREIGN KEY (reservacion_id) REFERENCES reservaciones(id) ON DELETE CASCADE,
  FOREIGN KEY (villa_id) REFERENCES villas(id)
) ENGINE=InnoDB;

CREATE TABLE reservacion_historial (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservacion_id INT UNSIGNED NOT NULL,
  estado_anterior VARCHAR(20) NULL,
  estado_nuevo VARCHAR(20) NOT NULL,
  nota VARCHAR(500) NULL,
  usuario_id INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reservacion_id) REFERENCES reservaciones(id) ON DELETE CASCADE,
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consecutivo anual para el número de reservación
CREATE TABLE folios (
  anio SMALLINT UNSIGNED PRIMARY KEY,
  ultimo INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- ---------- Bloqueos (sin reservación) ----------
CREATE TABLE bloqueos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  villa_id TINYINT UNSIGNED NOT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE NOT NULL,                       -- exclusiva
  motivo ENUM('mantenimiento','propietario','externo','otro') NOT NULL DEFAULT 'otro',
  nota VARCHAR(500) NULL,
  origen ENUM('manual','airbnb','vrbo','otro') NOT NULL DEFAULT 'manual',
  uid_externo VARCHAR(255) NULL,                 -- UID del evento iCal
  calendario_externo_id INT UNSIGNED NULL,
  creado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (fecha_fin > fecha_inicio),
  FOREIGN KEY (villa_id) REFERENCES villas(id),
  FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
  UNIQUE KEY uq_bloqueo_externo (villa_id, origen, uid_externo),
  INDEX idx_bloq_fechas (villa_id, fecha_inicio, fecha_fin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fase 4: enlaces iCal de Airbnb/VRBO (vacía por ahora)
CREATE TABLE calendarios_externos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  villa_id TINYINT UNSIGNED NOT NULL,
  plataforma ENUM('airbnb','vrbo','otro') NOT NULL,
  url_ical VARCHAR(1000) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  ultima_sincronizacion DATETIME NULL,
  ultimo_error VARCHAR(500) NULL,
  FOREIGN KEY (villa_id) REFERENCES villas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE bloqueos ADD FOREIGN KEY (calendario_externo_id)
  REFERENCES calendarios_externos(id) ON DELETE CASCADE;

-- ---------- Fase 3: administración ----------
CREATE TABLE categorias_movimiento (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('ingreso','egreso') NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  UNIQUE KEY (tipo, nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categorias_movimiento (tipo, nombre) VALUES
  ('ingreso','Renta'), ('ingreso','Depósito'), ('ingreso','Servicios extra'),
  ('egreso','Limpieza'), ('egreso','Mantenimiento'), ('egreso','Servicios (luz, agua, gas, internet)'),
  ('egreso','Nómina'), ('egreso','Comisiones plataforma'), ('egreso','Impuestos'), ('egreso','Otro');

CREATE TABLE movimientos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('ingreso','egreso') NOT NULL,
  categoria_id SMALLINT UNSIGNED NULL,
  villa_id TINYINT UNSIGNED NULL,                -- NULL = general / ambas
  reservacion_id INT UNSIGNED NULL,
  fecha DATE NOT NULL,
  concepto VARCHAR(255) NOT NULL,
  monto DECIMAL(12,2) NOT NULL,
  moneda CHAR(3) NOT NULL DEFAULT 'MXN',
  metodo VARCHAR(40) NULL,                       -- transferencia, efectivo, tarjeta…
  comprobante VARCHAR(255) NULL,                 -- ruta de archivo
  creado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (monto > 0),
  FOREIGN KEY (categoria_id) REFERENCES categorias_movimiento(id) ON DELETE SET NULL,
  FOREIGN KEY (villa_id) REFERENCES villas(id),
  FOREIGN KEY (reservacion_id) REFERENCES reservaciones(id) ON DELETE SET NULL,
  FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
  INDEX idx_mov_fecha (fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tareas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  titulo VARCHAR(200) NOT NULL,
  descripcion TEXT NULL,
  villa_id TINYINT UNSIGNED NULL,
  reservacion_id INT UNSIGNED NULL,
  asignado_a INT UNSIGNED NULL,
  fecha_limite DATE NULL,
  prioridad ENUM('baja','media','alta') NOT NULL DEFAULT 'media',
  estado ENUM('pendiente','en_proceso','hecha') NOT NULL DEFAULT 'pendiente',
  creado_por INT UNSIGNED NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completada_en DATETIME NULL,
  FOREIGN KEY (villa_id) REFERENCES villas(id),
  FOREIGN KEY (reservacion_id) REFERENCES reservaciones(id) ON DELETE SET NULL,
  FOREIGN KEY (asignado_a) REFERENCES usuarios(id) ON DELETE SET NULL,
  FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
  INDEX idx_tareas_estado (estado, fecha_limite)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;