-- ============================================================
-- DDL — Encuesta Territorial Ecatepec 2026
-- Base de datos: pollslat_encuesta_ecat
-- Dominio: polls.lat
-- Motor: MySQL 8.0 / InnoDB
-- Municipio: Ecatepec de Morelos, Estado de México
-- Secciones: 394 | Cuota total: 1200
-- Candidatos Modelo Morena (6):
--   A: Azucena Cisneros
--   B: Octavio Martínez
--   C: Rafael García Moreno
--   D: Zaira Cedillo Silva
--   E: Luzma Hernández Bermudes
--   F: Edmundo Valdeña Bastida
-- Generado: Mayo 2026 | IQ Comunicación / polls.lat
-- ============================================================


-- ------------------------------------------------------------
-- TABLA: secciones
-- 394 secciones electorales de Ecatepec
-- cuota total = 1200 cuestionarios
-- 18 secciones con cuota 4 | 376 secciones con cuota 3
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `secciones` (
  `id`              INT         NOT NULL AUTO_INCREMENT,
  `numero_seccion`  INT         NOT NULL,
  `cuota`           INT         NOT NULL DEFAULT 3,
  `capturadas`      INT         NOT NULL DEFAULT 0,
  `completada`      TINYINT(1)  NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_numero_seccion` (`numero_seccion`),
  INDEX `idx_completada` (`completada`, `capturadas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- TABLA: folios
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `folios` (
  `id`                INT         NOT NULL AUTO_INCREMENT,
  `folio`             CHAR(4)     NOT NULL,
  `encuestador_clave` VARCHAR(10) NOT NULL,
  `seccion`           INT         NOT NULL,
  `asignado_at`       TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completado`        TINYINT(1)  NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_folio` (`folio`),
  INDEX `idx_completado` (`completado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- TABLA: encuestas
-- Instrumento modelo MORENA — Ecatepec 2026
-- 6 candidatos: A=Azucena Cisneros, B=Octavio Martínez,
--               C=Rafael García Moreno, D=Zaira Cedillo Silva,
--               E=Luzma Hernández Bermudes, F=Edmundo Valdeña Bastida
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `encuestas` (
  `id`                    INT             NOT NULL AUTO_INCREMENT,
  `folio`                 CHAR(4)         NOT NULL,

  -- SECCIÓN INTERNO
  `fecha`                 DATE            NOT NULL,
  `encuestador_clave`     VARCHAR(10)     NOT NULL,
  `supervisor_clave`      VARCHAR(10)     NOT NULL,

  -- SECCIÓN FILTRO / GEOLOCALIZACIÓN
  `seccion_electoral`     INT             NOT NULL,
  `es_sustitucion`        TINYINT(1)      NOT NULL DEFAULT 0,
  `manzana`               VARCHAR(20)     DEFAULT NULL,
  `gps_lat`               DECIMAL(10,8)   DEFAULT NULL,
  `gps_lng`               DECIMAL(11,8)   DEFAULT NULL,
  `gps_precision`         FLOAT           DEFAULT NULL,
  `audio_path`            VARCHAR(255)    DEFAULT NULL,
  `p4_personas_mayores`   INT             DEFAULT NULL,
  `p5_mayor_edad`         VARCHAR(5)      DEFAULT NULL,
  `p6_credencial`         VARCHAR(5)      DEFAULT NULL,

  -- P7: Conocimiento previo ¿Conoce o ha escuchado hablar de...?
  -- Si / No / NS — por candidato A-F
  `p7_a` VARCHAR(5) DEFAULT NULL COMMENT 'Azucena Cisneros',
  `p7_b` VARCHAR(5) DEFAULT NULL COMMENT 'Octavio Martínez',
  `p7_c` VARCHAR(5) DEFAULT NULL COMMENT 'Rafael García Moreno',
  `p7_d` VARCHAR(5) DEFAULT NULL COMMENT 'Zaira Cedillo Silva',
  `p7_e` VARCHAR(5) DEFAULT NULL COMMENT 'Luzma Hernández Bermudes',
  `p7_f` VARCHAR(5) DEFAULT NULL COMMENT 'Edmundo Valdeña Bastida',

  -- P8a: Partido relacionado (identificación partidista)
  `p8a_a` VARCHAR(50) DEFAULT NULL,
  `p8a_b` VARCHAR(50) DEFAULT NULL,
  `p8a_c` VARCHAR(50) DEFAULT NULL,
  `p8a_d` VARCHAR(50) DEFAULT NULL,
  `p8a_e` VARCHAR(50) DEFAULT NULL,
  `p8a_f` VARCHAR(50) DEFAULT NULL,

  -- P8b: Opinión / Saldo de opinión (Buena / Regular / Mala / NS)
  `p8b_a` VARCHAR(10) DEFAULT NULL,
  `p8b_b` VARCHAR(10) DEFAULT NULL,
  `p8b_c` VARCHAR(10) DEFAULT NULL,
  `p8b_d` VARCHAR(10) DEFAULT NULL,
  `p8b_e` VARCHAR(10) DEFAULT NULL,
  `p8b_f` VARCHAR(10) DEFAULT NULL,

  -- P9: Honestidad (Mucho / Algo / Poco / Nada / NS)
  `p9_a` VARCHAR(10) DEFAULT NULL,
  `p9_b` VARCHAR(10) DEFAULT NULL,
  `p9_c` VARCHAR(10) DEFAULT NULL,
  `p9_d` VARCHAR(10) DEFAULT NULL,
  `p9_e` VARCHAR(10) DEFAULT NULL,
  `p9_f` VARCHAR(10) DEFAULT NULL,

  -- P10: Cercanía a la gente (Mucho / Algo / Poco / Nada / NC)
  `p10_a` VARCHAR(10) DEFAULT NULL,
  `p10_b` VARCHAR(10) DEFAULT NULL,
  `p10_c` VARCHAR(10) DEFAULT NULL,
  `p10_d` VARCHAR(10) DEFAULT NULL,
  `p10_e` VARCHAR(10) DEFAULT NULL,
  `p10_f` VARCHAR(10) DEFAULT NULL,

  -- P11: Conoce el municipio (Mucho / Algo / Poco / Nada / NC)
  `p11_a` VARCHAR(10) DEFAULT NULL,
  `p11_b` VARCHAR(10) DEFAULT NULL,
  `p11_c` VARCHAR(10) DEFAULT NULL,
  `p11_d` VARCHAR(10) DEFAULT NULL,
  `p11_e` VARCHAR(10) DEFAULT NULL,
  `p11_f` VARCHAR(10) DEFAULT NULL,

  -- P12: Cumple lo que dice (Mucho / Algo / Poco / Nada / NC)
  `p12_a` VARCHAR(10) DEFAULT NULL,
  `p12_b` VARCHAR(10) DEFAULT NULL,
  `p12_c` VARCHAR(10) DEFAULT NULL,
  `p12_d` VARCHAR(10) DEFAULT NULL,
  `p12_e` VARCHAR(10) DEFAULT NULL,
  `p12_f` VARCHAR(10) DEFAULT NULL,

  -- P13: Buen candidato (Sí / No / NS)
  `p13_a` VARCHAR(10) DEFAULT NULL,
  `p13_b` VARCHAR(10) DEFAULT NULL,
  `p13_c` VARCHAR(10) DEFAULT NULL,
  `p13_d` VARCHAR(10) DEFAULT NULL,
  `p13_e` VARCHAR(10) DEFAULT NULL,
  `p13_f` VARCHAR(10) DEFAULT NULL,

  -- P14: Disposición a votar (Sí votaría / No votaría / NS)
  `p14_a` VARCHAR(15) DEFAULT NULL,
  `p14_b` VARCHAR(15) DEFAULT NULL,
  `p14_c` VARCHAR(15) DEFAULT NULL,
  `p14_d` VARCHAR(15) DEFAULT NULL,
  `p14_e` VARCHAR(15) DEFAULT NULL,
  `p14_f` VARCHAR(15) DEFAULT NULL,

  -- P15: Preferencia como candidato MORENA (Prefiere / NS)
  `p15_a` VARCHAR(10) DEFAULT NULL,
  `p15_b` VARCHAR(10) DEFAULT NULL,
  `p15_c` VARCHAR(10) DEFAULT NULL,
  `p15_d` VARCHAR(10) DEFAULT NULL,
  `p15_e` VARCHAR(10) DEFAULT NULL,
  `p15_f` VARCHAR(10) DEFAULT NULL,

  -- P16: Intención de voto (MORENA/PRI/MC/PAN/PT/PVEM/PRD/NS/NR)
  `p16_partido`       VARCHAR(20)  DEFAULT NULL,

  -- P17: Identificación partidista
  `p17_partido`       VARCHAR(20)  DEFAULT NULL,

  -- SECCIÓN COMUNICACIÓN
  `p18_telefono`  VARCHAR(5)  DEFAULT NULL,
  `p19_whatsapp`  TINYINT(1)  NOT NULL DEFAULT 0,
  `p19_facebook`  TINYINT(1)  NOT NULL DEFAULT 0,
  `p19_youtube`   TINYINT(1)  NOT NULL DEFAULT 0,
  `p19_x`         TINYINT(1)  NOT NULL DEFAULT 0,
  `p19_tiktok`    TINYINT(1)  NOT NULL DEFAULT 0,

  -- CIERRE INTERNO
  `supervisor_cierre`   VARCHAR(10)   DEFAULT NULL,
  `encuestador_cierre`  VARCHAR(10)   DEFAULT NULL,
  `tipo_supervision`    VARCHAR(20)   DEFAULT NULL,

  -- METADATOS
  `created_at`    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip_address`    VARCHAR(45) DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_folio_enc` (`folio`),
  INDEX `idx_fecha`          (`fecha`),
  INDEX `idx_encuestador`    (`encuestador_clave`),
  INDEX `idx_seccion`        (`seccion_electoral`),
  INDEX `idx_p7`             (`p7_a`,`p7_b`,`p7_c`,`p7_d`,`p7_e`,`p7_f`),
  INDEX `idx_p8b`            (`p8b_a`,`p8b_b`,`p8b_c`,`p8b_d`,`p8b_e`,`p8b_f`),
  INDEX `idx_p9`             (`p9_a`,`p9_b`,`p9_c`,`p9_d`,`p9_e`,`p9_f`),
  INDEX `idx_p13`            (`p13_a`,`p13_b`,`p13_c`,`p13_d`,`p13_e`,`p13_f`),
  INDEX `idx_p14`            (`p14_a`,`p14_b`,`p14_c`,`p14_d`,`p14_e`,`p14_f`),
  INDEX `idx_p15`            (`p15_a`,`p15_b`,`p15_c`,`p15_d`,`p15_e`,`p15_f`),
  INDEX `idx_p16`            (`p16_partido`),
  INDEX `idx_p17`            (`p17_partido`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- SECCIONES ELECTORALES — Ecatepec de Morelos 2026
-- Fuente: ecatepec_secciones_encuesta.xlsx
-- 394 secciones | Cuota total: 1200
-- 18 secciones con cuota 4 | 376 secciones con cuota 3
-- ============================================================
INSERT INTO `secciones` (`numero_seccion`, `cuota`) VALUES
(1751, 4),
(1756, 3),
(1760, 3),
(1765, 3),
(1769, 4),
(1770, 3),
(1773, 3),
(1775, 3),
(1777, 4),
(1779, 3),
(1782, 3),
(1783, 3),
(1786, 3),
(1788, 3),
(1790, 3),
(1791, 3),
(1793, 3),
(1795, 3),
(1796, 4),
(1797, 3),
(1798, 3),
(1799, 3),
(1800, 3),
(1801, 3),
(1802, 3),
(1803, 3),
(1805, 3),
(1806, 3),
(1807, 4),
(1811, 3),
(1812, 3),
(1813, 3),
(1815, 3),
(1816, 3),
(1817, 3),
(1818, 3),
(1820, 3),
(1821, 3),
(1825, 3),
(1826, 3),
(1827, 3),
(1828, 3),
(1829, 3),
(1832, 3),
(1833, 3),
(1839, 3),
(6014, 3),
(6015, 3),
(6016, 3),
(6024, 3),
(6025, 3),
(6027, 3),
(6028, 3),
(6030, 3),
(6031, 4),
(6033, 3),
(6035, 3),
(6037, 3),
(6038, 3),
(6039, 3),
(6041, 4),
(6042, 3),
(6043, 3),
(6044, 3),
(6045, 3),
(6046, 3),
(6048, 3),
(6049, 3),
(6481, 3),
(6496, 3),
(6721, 3),
(6723, 4),
(6724, 3),
(6725, 3),
(1394, 3),
(1395, 3),
(1398, 3),
(1399, 3),
(1400, 3),
(1403, 4),
(1405, 3),
(1517, 3),
(1518, 3),
(1520, 3),
(1521, 3),
(1528, 3),
(1529, 3),
(1532, 4),
(1534, 3),
(1535, 3),
(1536, 3),
(1537, 3),
(1538, 3),
(1539, 3),
(1541, 4),
(1543, 3),
(1547, 3),
(1548, 3),
(1551, 3),
(1552, 3),
(1554, 3),
(1557, 3),
(1558, 3),
(1559, 3),
(1561, 3),
(1570, 3),
(1571, 3),
(1572, 4),
(1573, 3),
(1575, 3),
(1576, 3),
(1577, 3),
(1578, 3),
(1579, 3),
(1580, 3),
(1591, 3),
(1593, 4),
(1596, 3),
(1597, 3),
(1598, 3),
(1843, 3),
(1844, 3),
(1853, 3),
(1860, 3),
(1862, 3),
(1863, 4),
(1864, 3),
(1873, 3),
(1874, 3),
(1876, 3),
(1877, 3),
(1880, 3),
(1881, 3),
(1882, 3),
(1883, 3),
(1884, 3),
(1887, 3),
(1890, 3),
(1891, 3),
(1892, 3),
(1893, 3),
(1896, 3),
(1897, 3),
(1898, 3),
(1900, 3),
(1902, 3),
(1903, 3),
(1904, 3),
(1905, 3),
(1908, 3),
(1909, 3),
(1910, 3),
(1911, 3),
(1913, 3),
(1917, 3),
(1932, 3),
(1933, 3),
(1937, 3),
(1938, 3),
(1939, 3),
(1940, 3),
(1941, 3),
(1942, 3),
(1943, 3),
(1944, 3),
(1951, 3),
(0, 3),
(1305, 3),
(1306, 3),
(1307, 3),
(1308, 3),
(1310, 3),
(1311, 3),
(1312, 3),
(1313, 3),
(1314, 3),
(1317, 3),
(1318, 3),
(1323, 3),
(1324, 3),
(1325, 3),
(1327, 3),
(1328, 3),
(1339, 3),
(1347, 3),
(1348, 3),
(1349, 3),
(1351, 3),
(1352, 3),
(1353, 3),
(1356, 3),
(1359, 3),
(1363, 3),
(1364, 3),
(1506, 3),
(1508, 3),
(1515, 3),
(1524, 3),
(5994, 3),
(5999, 3),
(6001, 3),
(6002, 3),
(6004, 3),
(6011, 3),
(6019, 3),
(6022, 3),
(6023, 3),
(6213, 3),
(6215, 3),
(6216, 3),
(6217, 3),
(6220, 3),
(6221, 3),
(6224, 3),
(6225, 3),
(6226, 3),
(6726, 3),
(1373, 3),
(1374, 3),
(1375, 3),
(1378, 3),
(1380, 3),
(1381, 3),
(1384, 3),
(1388, 3),
(1389, 3),
(1392, 3),
(1396, 3),
(1397, 3),
(1407, 3),
(1408, 3),
(1411, 3),
(1412, 3),
(1415, 3),
(1417, 3),
(1418, 3),
(1419, 3),
(1421, 3),
(1422, 3),
(1434, 3),
(1435, 4),
(1437, 3),
(1448, 3),
(1450, 3),
(1451, 3),
(1452, 3),
(1469, 3),
(1471, 3),
(1489, 3),
(1490, 3),
(1491, 3),
(1493, 3),
(1494, 3),
(1497, 3),
(1610, 3),
(1612, 3),
(1410, 3),
(1420, 3),
(1424, 3),
(1425, 3),
(1426, 3),
(1427, 3),
(1429, 3),
(1432, 3),
(1433, 3),
(1441, 3),
(1442, 3),
(1443, 3),
(1444, 3),
(1445, 3),
(1447, 3),
(1453, 3),
(1454, 3),
(1455, 3),
(1456, 3),
(1458, 3),
(1461, 3),
(1462, 3),
(1463, 3),
(1464, 3),
(1465, 3),
(1473, 3),
(1475, 3),
(1476, 3),
(1479, 3),
(1482, 3),
(1483, 3),
(1484, 3),
(1485, 3),
(1487, 3),
(1488, 3),
(1499, 3),
(1500, 3),
(1545, 3),
(1546, 3),
(1566, 3),
(1582, 3),
(1583, 3),
(1584, 3),
(1585, 3),
(1586, 3),
(1587, 3),
(1590, 3),
(1599, 3),
(1601, 3),
(1602, 3),
(1603, 3),
(1604, 3),
(1605, 3),
(1607, 3),
(1608, 3),
(1609, 3),
(1614, 3),
(1615, 3),
(1616, 3),
(1618, 3),
(1620, 3),
(1621, 3),
(1622, 3),
(1623, 3),
(1624, 3),
(1625, 3),
(1626, 3),
(1627, 3),
(1628, 3),
(1630, 3),
(1633, 3),
(1635, 3),
(1637, 3),
(1638, 3),
(1642, 3),
(1643, 3),
(1644, 3),
(1645, 3),
(1647, 3),
(1649, 3),
(1650, 3),
(1651, 3),
(1652, 4),
(1653, 3),
(1654, 3),
(1655, 3),
(1656, 3),
(1657, 3),
(1658, 3),
(1659, 3),
(1661, 3),
(1663, 3),
(1664, 3),
(1665, 3),
(1666, 3),
(1667, 3),
(1670, 3),
(1671, 3),
(1672, 3),
(1674, 3),
(1675, 3),
(1677, 3),
(1678, 3),
(1679, 3),
(1681, 3),
(1682, 3),
(1685, 3),
(1686, 3),
(1687, 3),
(1688, 3),
(1689, 3),
(1690, 3),
(1691, 3),
(1692, 3),
(1693, 3),
(1695, 3),
(1696, 3),
(1697, 3),
(1699, 3),
(1700, 3),
(1703, 3),
(1704, 3),
(1705, 4),
(1707, 3),
(1708, 3),
(1709, 3),
(1710, 4),
(1712, 3),
(1713, 3),
(1714, 3),
(1720, 3),
(1721, 3),
(1723, 3),
(1724, 3),
(1727, 3),
(1730, 3),
(1732, 3),
(1734, 3)
ON DUPLICATE KEY UPDATE `cuota` = VALUES(`cuota`);

-- ============================================================
-- FIN DEL DDL
-- ============================================================
