-- crear_tablas_fotografiascomercio.sql
-- Crea tablas para importar fotografiascomercio.csv y almacenar fotos con GPS.
-- Requiere MySQL 5.7+ / 8.0 (utf8mb4).

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- 1) Tabla base (import del CSV)
DROP TABLE IF EXISTS comercios;
CREATE TABLE comercios (
  id              INT NOT NULL,
  nombre_completo VARCHAR(255) NOT NULL,
  tipo_cliente    VARCHAR(80)  NULL,
  application_id  INT          NULL,
  estatus         VARCHAR(40)  NULL,
  fecha_registro  DATETIME     NULL,
  PRIMARY KEY (id),
  KEY idx_comercios_nombre (nombre_completo),
  KEY idx_comercios_app (application_id),
  KEY idx_comercios_estatus (estatus),
  KEY idx_comercios_fecha (fecha_registro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Tabla de evidencias fotográficas (subidas/capturadas desde el PHP)
DROP TABLE IF EXISTS comercio_fotos;
CREATE TABLE comercio_fotos (
  foto_id      BIGINT NOT NULL AUTO_INCREMENT,
  comercio_id  INT    NOT NULL,
  image_path   VARCHAR(500) NOT NULL,
  mime_type    VARCHAR(80)  NULL,
  file_size    INT          NULL,

  lat          DECIMAL(10,7) NULL,
  lng          DECIMAL(10,7) NULL,
  accuracy_m   DECIMAL(10,2) NULL,

  taken_at     DATETIME NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  notes        VARCHAR(255) NULL,
  PRIMARY KEY (foto_id),
  KEY idx_fotos_comercio (comercio_id),
  KEY idx_fotos_taken (taken_at),
  CONSTRAINT fk_fotos_comercio
    FOREIGN KEY (comercio_id) REFERENCES comercios(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) Importación del CSV (opcional)
-- IMPORTANTE:
--  - Ajusta la ruta completa del archivo CSV.
--  - En XAMPP suele ser algo como: C:/xampp/htdocs/tu_proyecto/fotografiascomercio.csv
--  - Si tu servidor no permite LOCAL, quita LOCAL y coloca el CSV en el directorio permitido por MySQL.
--
-- Si el CSV tiene encabezados (sí los tiene), se ignora la primera línea.
--
-- Nota: La fecha viene como "DD/MM/YYYY HH:MM" (ej. 23/12/2025 20:44)
-- y se transforma a DATETIME con STR_TO_DATE.

-- SET @csv_path = 'C:/RUTA/ABSOLUTA/fotografiascomercio.csv';

-- LOAD DATA LOCAL INFILE @csv_path
-- INTO TABLE comercios
-- CHARACTER SET utf8mb4
-- FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-- LINES TERMINATED BY '\n'
-- IGNORE 1 LINES
-- (id, nombre_completo, tipo_cliente, application_id, estatus, @fecha_registro_str)
-- SET fecha_registro = STR_TO_DATE(@fecha_registro_str, '%d/%m/%Y %H:%i');