-- crear_tablas_indicadores.sql
SET NAMES utf8mb4;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `afiliaciones`;
DROP TABLE IF EXISTS `transacciones`;
DROP TABLE IF EXISTS `usuarios`;
DROP TABLE IF EXISTS `zonas`;

CREATE TABLE `usuarios` (
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `nombre_completo` VARCHAR(120) NULL,
  `email` VARCHAR(180) NULL,
  `rol` VARCHAR(40) NULL,
  `estatus` VARCHAR(20) NULL,
  `fecha_registro` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `zonas` (
  `id` INT UNSIGNED NOT NULL,
  `nombre` VARCHAR(120) NOT NULL,
  `descripcion` VARCHAR(255) NULL,
  `dia_pago` VARCHAR(20) NULL,
  `application_id` INT UNSIGNED NULL,
  `fecha_registro` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_application_id` (`application_id`),
  KEY `idx_dia_pago` (`dia_pago`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `afiliaciones` (
  `id` INT UNSIGNED NOT NULL,
  `cliente_id` INT UNSIGNED NOT NULL,
  `unidad_id` INT UNSIGNED NOT NULL,
  `tipo_afiliacion` VARCHAR(60) NULL,
  `application_id` INT UNSIGNED NULL,
  `importe` DECIMAL(12,2) NULL,
  `fecha_afiliacion` DATE NULL,
  `fecha_inicio_pago` DATE NULL,
  `concepto_pago` INT UNSIGNED NULL,
  `observaciones` TEXT NULL,
  `estatus` VARCHAR(20) NULL,
  `fecha_registro` DATETIME NULL,
  `tianguis_id` INT UNSIGNED NULL,
  `giro` VARCHAR(80) NULL,
  `metros_cuadrados` DECIMAL(10,2) NULL,
  `monto_cobrado` DECIMAL(12,2) NULL,
  `monto_basura` DECIMAL(12,2) NULL,
  `expediente_documentos` TEXT NULL,
  `apoyo_tercera_edad` TINYINT(1) NOT NULL DEFAULT 0,
  `dia_pago` VARCHAR(20) NULL,
  `anden` VARCHAR(80) NULL,
  `linea` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cliente_id` (`cliente_id`),
  KEY `idx_tianguis_id` (`tianguis_id`),
  KEY `idx_linea` (`linea`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transacciones` (
  `id` INT UNSIGNED NOT NULL,
  `cliente_id` INT UNSIGNED NULL,
  `application_id` INT UNSIGNED NULL,
  `unidad_id` INT UNSIGNED NULL,
  `concepto_id` INT UNSIGNED NULL,
  `tipo_movimiento` VARCHAR(30) NULL,
  `monto` DECIMAL(12,2) NULL,
  `fecha` DATE NULL,
  `estado` VARCHAR(30) NULL,
  `observaciones` TEXT NULL,
  `fecha_registro` DATETIME NULL,
  `latitud` DECIMAL(10,7) NULL,
  `longitud` DECIMAL(10,7) NULL,
  `aplicado_por` INT UNSIGNED NULL,
  `corte_id` INT UNSIGNED NULL,
  `concepto_extra` VARCHAR(255) NULL,
  `provisional_id` INT UNSIGNED NULL,
  `zona_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cliente_id` (`cliente_id`),
  KEY `idx_zona_id` (`zona_id`),
  KEY `idx_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;