-- ============================================================
--  EggChecker — Modelo Físico para MySQL 8
--  Ejecutar en phpMyAdmin (pestaña SQL) o por consola:
--     mysql -u root -p < eggchecker_schema.sql
--
--  Crea la base de datos `eggchecker`, las 16 tablas, los índices,
--  las 3 vistas y los catálogos fijos (tipo_huevo, categoria_insumo).
--  Es idempotente: elimina y vuelve a crear los objetos.
-- ============================================================

CREATE DATABASE IF NOT EXISTS `eggchecker`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `eggchecker`;

SET FOREIGN_KEY_CHECKS = 0;

DROP VIEW IF EXISTS `v_produccion_detallada`;
DROP VIEW IF EXISTS `v_alertas_insumo`;
DROP VIEW IF EXISTS `v_pedidos_pendientes`;

DROP TABLE IF EXISTS `notificacion`;
DROP TABLE IF EXISTS `analisis_ia`;
DROP TABLE IF EXISTS `detalle_pedido`;
DROP TABLE IF EXISTS `pedido`;
DROP TABLE IF EXISTS `cliente`;
DROP TABLE IF EXISTS `stock_produccion`;
DROP TABLE IF EXISTS `produccion_detalle`;
DROP TABLE IF EXISTS `produccion_diaria`;
DROP TABLE IF EXISTS `tipo_huevo`;
DROP TABLE IF EXISTS `evento_sanitario`;
DROP TABLE IF EXISTS `camada`;
DROP TABLE IF EXISTS `movimiento_insumo`;
DROP TABLE IF EXISTS `insumo`;
DROP TABLE IF EXISTS `categoria_insumo`;
DROP TABLE IF EXISTS `token_recuperacion`;
DROP TABLE IF EXISTS `usuario`;

SET FOREIGN_KEY_CHECKS = 1;


-- ─────────────────────────────────────────────────────────────
-- Table usuario
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `usuario` (
  `id_usuario`              INT          NOT NULL AUTO_INCREMENT,
  `nombre_completo`         VARCHAR(100) NOT NULL,
  `correo_electronico`      VARCHAR(100) NOT NULL,
  `contrasena_hash`         VARCHAR(255) NOT NULL,
  `telefono`                VARCHAR(20)  NULL,
  `nombre_granja`           VARCHAR(100) NULL,
  `plan_suscripcion`        VARCHAR(20)  NOT NULL DEFAULT 'gratuito',
  `fecha_registro`          DATE         NOT NULL,
  `activo`                  TINYINT(1)   NOT NULL DEFAULT 1,
  `notif_produccion_baja`   TINYINT(1)   NOT NULL DEFAULT 1,
  `notif_stock_bajo`        TINYINT(1)   NOT NULL DEFAULT 1,
  `notif_vacunacion`        TINYINT(1)   NOT NULL DEFAULT 0,
  `notif_resumen_semanal`   TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`id_usuario`),
  UNIQUE KEY `uq_usuario_correo` (`correo_electronico`),
  CONSTRAINT `chk_usuario_plan`
    CHECK (`plan_suscripcion` IN ('gratuito','premium'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table token_recuperacion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `token_recuperacion` (
  `id_token`         INT         NOT NULL AUTO_INCREMENT,
  `id_usuario`       INT         NOT NULL,
  `token_hash`       VARCHAR(64) NOT NULL,
  `fecha_creacion`   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_expiracion` DATETIME    NOT NULL,
  `usado`            TINYINT(1)  NOT NULL DEFAULT 0,
  PRIMARY KEY (`id_token`),
  KEY `idx_token_usuario` (`id_usuario`),
  KEY `idx_token_hash` (`token_hash`),
  CONSTRAINT `fk_token_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table categoria_insumo
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `categoria_insumo` (
  `id_categoria` INT          NOT NULL AUTO_INCREMENT,
  `nombre_categ` VARCHAR(60)  NOT NULL,
  `descripcion`  VARCHAR(200) NULL,
  PRIMARY KEY (`id_categoria`),
  UNIQUE KEY `uq_categoria_nombre` (`nombre_categ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table insumo
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `insumo` (
  `id_insumo`      INT           NOT NULL AUTO_INCREMENT,
  `id_usuario`     INT           NOT NULL,
  `id_categoria`   INT           NOT NULL,
  `nombre_insumo`  VARCHAR(80)   NOT NULL,
  `unidad_medida`  VARCHAR(20)   NOT NULL,
  `stock_actual`   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `umbral_minimo`  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `costo_unitario` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `activo`         TINYINT(1)    NOT NULL DEFAULT 1,
  `descontinuado`  TINYINT(1)    NOT NULL DEFAULT 0,
  PRIMARY KEY (`id_insumo`),
  KEY `idx_insumo_usuario` (`id_usuario`),
  KEY `idx_insumo_categoria` (`id_categoria`),
  CONSTRAINT `fk_insumo_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_insumo_categoria`
    FOREIGN KEY (`id_categoria`) REFERENCES `categoria_insumo` (`id_categoria`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_insumo_stock`   CHECK (`stock_actual` >= 0),
  CONSTRAINT `chk_insumo_umbral`  CHECK (`umbral_minimo` >= 0),
  CONSTRAINT `chk_insumo_costo`   CHECK (`costo_unitario` >= 0),
  CONSTRAINT `chk_insumo_descontinuado` CHECK (`descontinuado` IN (0,1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table movimiento_insumo
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `movimiento_insumo` (
  `id_movimiento`    INT           NOT NULL AUTO_INCREMENT,
  `id_insumo`        INT           NOT NULL,
  `tipo_movimiento`  VARCHAR(10)   NOT NULL,
  `cantidad`         DECIMAL(10,2) NOT NULL,
  `costo_unitario`   DECIMAL(12,2) NULL,
  `fecha_movimiento` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `observaciones`    VARCHAR(255)  NULL,
  PRIMARY KEY (`id_movimiento`),
  KEY `idx_movimiento_insumo` (`id_insumo`),
  KEY `idx_movimiento_fecha` (`fecha_movimiento` DESC),
  CONSTRAINT `fk_movimiento_insumo`
    FOREIGN KEY (`id_insumo`) REFERENCES `insumo` (`id_insumo`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_movimiento_tipo`     CHECK (`tipo_movimiento` IN ('entrada','salida')),
  CONSTRAINT `chk_movimiento_cantidad` CHECK (`cantidad` > 0),
  CONSTRAINT `chk_movimiento_costo`    CHECK (`costo_unitario` IS NULL OR `costo_unitario` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table camada
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `camada` (
  `id_camada`          INT         NOT NULL AUTO_INCREMENT,
  `id_usuario`         INT         NOT NULL,
  `nombre_camada`      VARCHAR(60) NOT NULL,
  `fecha_ingreso`      DATE        NOT NULL,
  `cantidad_inicial`   INT         NOT NULL,
  `cantidad_actual`    INT         NOT NULL,
  `estado`             VARCHAR(10) NOT NULL DEFAULT 'activa',
  `edad_semanas`       INT         NOT NULL DEFAULT 16,
  `fecha_proximo_aviso` DATE       NULL,
  `fecha_creacion`     DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_camada`),
  KEY `idx_camada_usuario` (`id_usuario`),
  CONSTRAINT `fk_camada_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_camada_estado`     CHECK (`estado` IN ('activa','retirada')),
  CONSTRAINT `chk_camada_cant_ini`   CHECK (`cantidad_inicial` > 0),
  CONSTRAINT `chk_camada_cant_act`   CHECK (`cantidad_actual` >= 0),
  CONSTRAINT `chk_camada_cant_lte`   CHECK (`cantidad_actual` <= `cantidad_inicial`),
  CONSTRAINT `chk_camada_edad`       CHECK (`edad_semanas` >= 16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table evento_sanitario
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `evento_sanitario` (
  `id_evento`    INT          NOT NULL AUTO_INCREMENT,
  `id_camada`    INT          NOT NULL,
  `tipo_evento`  VARCHAR(20)  NOT NULL,
  `fecha_evento` DATE         NOT NULL,
  `descripcion`  VARCHAR(255) NOT NULL,
  `mortalidad`   INT          NOT NULL DEFAULT 0,
  PRIMARY KEY (`id_evento`),
  KEY `idx_evento_camada` (`id_camada`),
  KEY `idx_evento_fecha` (`fecha_evento` DESC),
  CONSTRAINT `fk_evento_camada`
    FOREIGN KEY (`id_camada`) REFERENCES `camada` (`id_camada`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_evento_tipo`       CHECK (`tipo_evento` IN ('vacunacion','mortalidad','enfermedad','otro')),
  CONSTRAINT `chk_evento_mortalidad` CHECK (`mortalidad` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table tipo_huevo
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `tipo_huevo` (
  `id_tipo`     INT         NOT NULL AUTO_INCREMENT,
  `nombre_tipo` VARCHAR(20) NOT NULL,
  `descripcion` VARCHAR(255) NULL,
  PRIMARY KEY (`id_tipo`),
  UNIQUE KEY `uq_tipo_huevo_nombre` (`nombre_tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table produccion_diaria
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `produccion_diaria` (
  `id_produccion`     INT         NOT NULL AUTO_INCREMENT,
  `id_usuario`        INT         NOT NULL,
  `id_camada`         INT         NOT NULL,
  `fecha_recoleccion` DATE        NOT NULL,
  `total_huevos`      INT         NOT NULL DEFAULT 0,
  `observaciones`     VARCHAR(255) NULL,
  PRIMARY KEY (`id_produccion`),
  UNIQUE KEY `uq_prod_usuario_camada_fecha` (`id_usuario`, `id_camada`, `fecha_recoleccion`),
  KEY `idx_prod_usuario` (`id_usuario`),
  KEY `idx_prod_camada` (`id_camada`),
  KEY `idx_prod_fecha` (`fecha_recoleccion` DESC),
  CONSTRAINT `fk_prod_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_prod_camada`
    FOREIGN KEY (`id_camada`) REFERENCES `camada` (`id_camada`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_prod_total` CHECK (`total_huevos` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table produccion_detalle
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `produccion_detalle` (
  `id_detalle`    INT NOT NULL AUTO_INCREMENT,
  `id_produccion` INT NOT NULL,
  `id_tipo`       INT NOT NULL,
  `cantidad`      INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id_detalle`),
  UNIQUE KEY `uq_prodet_prod_tipo` (`id_produccion`, `id_tipo`),
  KEY `idx_prodet_produccion` (`id_produccion`),
  KEY `idx_prodet_tipo` (`id_tipo`),
  CONSTRAINT `fk_prodet_produccion`
    FOREIGN KEY (`id_produccion`) REFERENCES `produccion_diaria` (`id_produccion`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_prodet_tipo`
    FOREIGN KEY (`id_tipo`) REFERENCES `tipo_huevo` (`id_tipo`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_prodet_cantidad` CHECK (`cantidad` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table stock_produccion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `stock_produccion` (
  `id_stock`            INT           NOT NULL AUTO_INCREMENT,
  `id_usuario`          INT           NOT NULL,
  `id_tipo`             INT           NOT NULL,
  `cantidad_actual`     INT           NOT NULL DEFAULT 0,
  `valor_unidad`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `ultima_modificacion` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_stock`),
  UNIQUE KEY `uq_stock_usuario_tipo` (`id_usuario`, `id_tipo`),
  KEY `idx_stock_usuario` (`id_usuario`),
  KEY `idx_stock_tipo` (`id_tipo`),
  CONSTRAINT `fk_stock_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_stock_tipo`
    FOREIGN KEY (`id_tipo`) REFERENCES `tipo_huevo` (`id_tipo`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_stock_cantidad` CHECK (`cantidad_actual` >= 0),
  CONSTRAINT `chk_stock_valor`    CHECK (`valor_unidad` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table cliente
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `cliente` (
  `id_cliente`          INT          NOT NULL AUTO_INCREMENT,
  `id_usuario`          INT          NOT NULL,
  `nombre_cliente`      VARCHAR(80)  NOT NULL,
  `telefono`            VARCHAR(20)  NULL,
  `direccion`           VARCHAR(200) NULL,
  `fecha_ultima_compra` DATE         NULL,
  `activo`              TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`id_cliente`),
  KEY `idx_cliente_usuario` (`id_usuario`),
  KEY `idx_cliente_ultima_compra` (`fecha_ultima_compra` DESC),
  CONSTRAINT `fk_cliente_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table pedido
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `pedido` (
  `id_pedido`     INT           NOT NULL AUTO_INCREMENT,
  `id_cliente`    INT           NOT NULL,
  `id_usuario`    INT           NOT NULL,
  `fecha_pedido`  DATE          NOT NULL,
  `estado_pedido` VARCHAR(10)   NOT NULL DEFAULT 'pendiente',
  `valor_total`   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id_pedido`),
  KEY `idx_pedido_cliente` (`id_cliente`),
  KEY `idx_pedido_usuario` (`id_usuario`),
  KEY `idx_pedido_fecha` (`fecha_pedido` DESC),
  KEY `idx_pedido_estado` (`estado_pedido`),
  CONSTRAINT `fk_pedido_cliente`
    FOREIGN KEY (`id_cliente`) REFERENCES `cliente` (`id_cliente`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_pedido_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_pedido_estado` CHECK (`estado_pedido` IN ('pendiente','enviado','recibido','cancelado')),
  CONSTRAINT `chk_pedido_valor`  CHECK (`valor_total` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table detalle_pedido
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `detalle_pedido` (
  `id_detalle_pedido` INT           NOT NULL AUTO_INCREMENT,
  `id_pedido`         INT           NOT NULL,
  `id_tipo`           INT           NOT NULL,
  `cantidad`          INT           NOT NULL,
  `precio_unitario`   DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id_detalle_pedido`),
  UNIQUE KEY `uq_detped_pedido_tipo` (`id_pedido`, `id_tipo`),
  KEY `idx_detped_pedido` (`id_pedido`),
  KEY `idx_detped_tipo` (`id_tipo`),
  CONSTRAINT `fk_detped_pedido`
    FOREIGN KEY (`id_pedido`) REFERENCES `pedido` (`id_pedido`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_detped_tipo`
    FOREIGN KEY (`id_tipo`) REFERENCES `tipo_huevo` (`id_tipo`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_detped_cantidad` CHECK (`cantidad` > 0),
  CONSTRAINT `chk_detped_precio`   CHECK (`precio_unitario` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table analisis_ia
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `analisis_ia` (
  `id_analisis`           INT          NOT NULL AUTO_INCREMENT,
  `id_usuario`            INT          NOT NULL,
  `id_camada`             INT          NULL,
  `fecha_analisis`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `imagen_url`            VARCHAR(500) NOT NULL,
  `resultado_diagnostico` TEXT         NOT NULL,
  `recomendaciones`       TEXT         NULL,
  `calidad_general`       VARCHAR(10)  NULL,
  `puntaje_calidad`       INT          NULL,
  `apto_venta`            TINYINT(1)   NULL,
  `huevos_detectados`     INT          NULL,
  `detalle_json`          LONGTEXT     NULL,
  `proveedor_ia`          VARCHAR(60)  NULL,
  `diagnostico_correcto`  TINYINT(1)   NULL,
  PRIMARY KEY (`id_analisis`),
  KEY `idx_analisis_usuario` (`id_usuario`),
  KEY `idx_analisis_camada` (`id_camada`),
  KEY `idx_analisis_fecha` (`fecha_analisis` DESC),
  CONSTRAINT `fk_analisis_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_analisis_camada`
    FOREIGN KEY (`id_camada`) REFERENCES `camada` (`id_camada`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `chk_analisis_calidad` CHECK (`calidad_general` IS NULL OR `calidad_general` IN ('buena','regular','mala')),
  CONSTRAINT `chk_analisis_puntaje` CHECK (`puntaje_calidad` IS NULL OR `puntaje_calidad` BETWEEN 0 AND 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- Table notificacion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `notificacion` (
  `id_notificacion`  INT           NOT NULL AUTO_INCREMENT,
  `id_usuario`       INT           NOT NULL,
  `id_camada`        INT           NULL,
  `id_insumo`        INT           NULL,
  `tipo`             VARCHAR(30)   NOT NULL,
  `titulo`           VARCHAR(120)  NOT NULL,
  `mensaje`          VARCHAR(255)  NOT NULL,
  `leida`            TINYINT(1)    NOT NULL DEFAULT 0,
  `eliminada`        TINYINT(1)    NOT NULL DEFAULT 0,
  `stock_referencia` DECIMAL(10,2) NULL,
  `fecha_creacion`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_notificacion`),
  UNIQUE KEY `uq_notificacion_usuario_camada_tipo` (`id_usuario`, `id_camada`, `tipo`),
  KEY `idx_notificacion_usuario` (`id_usuario`, `leida`),
  CONSTRAINT `fk_notificacion_usuario`
    FOREIGN KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_notificacion_camada`
    FOREIGN KEY (`id_camada`) REFERENCES `camada` (`id_camada`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_notificacion_insumo`
    FOREIGN KEY (`id_insumo`) REFERENCES `insumo` (`id_insumo`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `chk_notificacion_leida`     CHECK (`leida` IN (0,1)),
  CONSTRAINT `chk_notificacion_eliminada` CHECK (`eliminada` IN (0,1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
-- VISTAS
-- ─────────────────────────────────────────────────────────────
CREATE VIEW `v_produccion_detallada` AS
SELECT
    pd.id_produccion,
    u.nombre_completo          AS avicultor,
    c.nombre_camada,
    pd.fecha_recoleccion,
    pd.total_huevos,
    th.nombre_tipo             AS tipo_huevo,
    pdd.cantidad               AS cantidad_tipo
FROM  produccion_diaria  pd
JOIN  usuario            u   ON pd.id_usuario    = u.id_usuario
JOIN  camada             c   ON pd.id_camada     = c.id_camada
JOIN  produccion_detalle pdd ON pd.id_produccion = pdd.id_produccion
JOIN  tipo_huevo         th  ON pdd.id_tipo      = th.id_tipo
ORDER BY pd.fecha_recoleccion DESC, th.nombre_tipo ASC;

CREATE VIEW `v_alertas_insumo` AS
SELECT
    i.id_insumo,
    u.nombre_completo  AS avicultor,
    ci.nombre_categ    AS categoria,
    i.nombre_insumo,
    i.stock_actual,
    i.umbral_minimo,
    (i.umbral_minimo - i.stock_actual) AS deficit
FROM  insumo           i
JOIN  usuario          u  ON i.id_usuario   = u.id_usuario
JOIN  categoria_insumo ci ON i.id_categoria = ci.id_categoria
WHERE i.stock_actual < i.umbral_minimo
  AND i.activo = 1
ORDER BY deficit DESC;

CREATE VIEW `v_pedidos_pendientes` AS
SELECT
    p.id_pedido,
    u.nombre_completo  AS avicultor,
    cl.nombre_cliente  AS cliente,
    cl.telefono        AS telefono_cliente,
    p.fecha_pedido,
    p.valor_total,
    COUNT(dp.id_detalle_pedido) AS num_items
FROM  pedido         p
JOIN  usuario        u  ON p.id_usuario  = u.id_usuario
JOIN  cliente        cl ON p.id_cliente  = cl.id_cliente
JOIN  detalle_pedido dp ON p.id_pedido   = dp.id_pedido
WHERE p.estado_pedido = 'pendiente'
GROUP BY p.id_pedido, u.nombre_completo, cl.nombre_cliente,
         cl.telefono, p.fecha_pedido, p.valor_total
ORDER BY p.fecha_pedido ASC;


-- ─────────────────────────────────────────────────────────────
-- CATÁLOGOS FIJOS
-- ─────────────────────────────────────────────────────────────
INSERT INTO `tipo_huevo` (`nombre_tipo`, `descripcion`) VALUES
  ('AA',      'Huevo de primera calidad: cáscara dura, tamaño grande, sin defectos visibles'),
  ('A',       'Huevo de buena calidad: pequeñas imperfecciones en cáscara o tamaño'),
  ('B',       'Huevo con defectos visibles: cáscara rugosa, tamaño pequeño o manchas'),
  ('No_apto', 'Huevo roto, con cáscara blanda, deformaciones graves o contaminado');

INSERT INTO `categoria_insumo` (`nombre_categ`, `descripcion`) VALUES
  ('alimento',    'Concentrados, maíz, forraje y demás alimentos para las aves'),
  ('medicamento', 'Antibióticos, antiparasitarios y medicamentos veterinarios'),
  ('vitamina',    'Suplementos vitamínicos y minerales para las aves'),
  ('herramienta', 'Equipos y utensilios para el manejo del galpón'),
  ('limpieza',    'Desinfectantes, detergentes y materiales de aseo'),
  ('empaque',     'Cartones, canastas y embalajes para la distribución de huevos');

-- ============================================================
-- FIN DEL ESQUEMA MySQL — EggChecker v1.0
-- ============================================================