-- ============================================================================
-- CASO DE ESTUDIO: CAFÉ DEL NORESTE
-- VERSIÓN ADAPTADA PARA EDITORES EN LÍNEA (MYCOMPILER / DB-FIDDLE)
-- ============================================================================

-- ============================================================================
-- INCISOS i y ii: SENTENCIAS DDL (CREACIÓN DE TABLAS, PK, FK Y REGLAS)
-- ============================================================================

CREATE TABLE sucursales (
    id_sucursal VARCHAR(10) NOT NULL,
    nombre_sucursal VARCHAR(50) NOT NULL,
    direccion_sucursal VARCHAR(150) NOT NULL,
    telefono_sucursal VARCHAR(15) NOT NULL,
    CONSTRAINT pk_sucursales PRIMARY KEY (id_sucursal),
    CONSTRAINT uq_nombre_sucursal UNIQUE (nombre_sucursal)
);

CREATE TABLE proveedores (
    id_proveedor VARCHAR(10) NOT NULL,
    nombre_proveedor VARCHAR(100) NOT NULL,
    direccion_proveedor VARCHAR(150) NOT NULL,
    telefono_proveedor VARCHAR(15) NOT NULL,
    CONSTRAINT pk_proveedores PRIMARY KEY (id_proveedor),
    CONSTRAINT uq_nombre_proveedor UNIQUE (nombre_proveedor)
);

CREATE TABLE productos (
    id_producto VARCHAR(10) NOT NULL,
    nombre_producto VARCHAR(80) NOT NULL,
    unidad_medida VARCHAR(20) NOT NULL,
    CONSTRAINT pk_productos PRIMARY KEY (id_producto),
    CONSTRAINT uq_nombre_producto UNIQUE (nombre_producto),
    CONSTRAINT chk_unidad_medida CHECK (unidad_medida IN ('Kilos', 'Litros', 'Cajas', 'Piezas'))
);

CREATE TABLE inventario (
    id_inventario VARCHAR(10) NOT NULL,
    id_sucursal VARCHAR(10) NOT NULL,
    id_producto VARCHAR(10) NOT NULL,
    id_proveedor VARCHAR(10) NOT NULL,
    stock_actual INT NOT NULL DEFAULT 0,
    CONSTRAINT pk_inventario PRIMARY KEY (id_inventario),
    CONSTRAINT fk_inv_sucursal FOREIGN KEY (id_sucursal) 
        REFERENCES sucursales(id_sucursal)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_inv_producto FOREIGN KEY (id_producto) 
        REFERENCES productos(id_producto)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_inv_proveedor FOREIGN KEY (id_proveedor) 
        REFERENCES proveedores(id_proveedor)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_stock_no_negativo CHECK (stock_actual >= 0),
    CONSTRAINT uq_sucursal_producto_prov UNIQUE (id_sucursal, id_producto, id_proveedor)
);

-- ============================================================================
-- INCISOS iii, iv y v: SENTENCIAS DML (INSERCIÓN DE DATOS DEPURADOS)
-- ============================================================================

INSERT INTO sucursales (id_sucursal, nombre_sucursal, direccion_sucursal, telefono_sucursal) VALUES
('SUC-01', 'Nuevo León', 'Av. Las Américas 123, Colonia Anzures', '4882345678'),
('SUC-02', 'Coahuila', 'Calle de las Rosas 235, Colonia Bugambilias', '6714678932'),
('SUC-03', 'Tamaulipas', 'Avenida Río Grande 1240, Colonia Aranjuez', '8312346754');

INSERT INTO proveedores (id_proveedor, nombre_proveedor, direccion_proveedor, telefono_proveedor) VALUES
('PROV-01', 'Insumos Básicos S.A.', 'Miguel de Cervantes Saavedra 321, Fraccionamiento Las Nubes', '8132345230'),
('PROV-02', 'Abarrotes del Norte', 'Calle Negrete 1015, Colonia Anáhuac', '8197845321'),
('PROV-03', 'Proveedores del Noreste', 'Calle Pasteur 320, Colonia Los Reyes', '8175609234');

INSERT INTO productos (id_producto, nombre_producto, unidad_medida) VALUES
('PROD-01', 'Café regular', 'Kilos'),
('PROD-02', 'Café descafeinado', 'Kilos'),
('PROD-03', 'Té negro', 'Cajas'),
('PROD-04', 'Azúcar', 'Kilos'),
('PROD-05', 'Leche entera', 'Litros'),
('PROD-06', 'Leche deslactosada', 'Litros'),
('PROD-07', 'Leche condensada', 'Cajas');

INSERT INTO inventario (id_inventario, id_sucursal, id_producto, id_proveedor, stock_actual) VALUES
('INV-01', 'SUC-01', 'PROD-01', 'PROV-01', 3),
('INV-02', 'SUC-01', 'PROD-02', 'PROV-01', 2),
('INV-03', 'SUC-02', 'PROD-03', 'PROV-02', 5),
('INV-04', 'SUC-03', 'PROD-03', 'PROV-01', 3),
('INV-05', 'SUC-01', 'PROD-03', 'PROV-03', 6),
('INV-06', 'SUC-03', 'PROD-01', 'PROV-01', 3),
('INV-07', 'SUC-03', 'PROD-02', 'PROV-01', 5),
('INV-08', 'SUC-02', 'PROD-02', 'PROV-02', 10),
('INV-09', 'SUC-01', 'PROD-04', 'PROV-03', 20),
('INV-10', 'SUC-01', 'PROD-05', 'PROV-02', 25),
('INV-11', 'SUC-01', 'PROD-06', 'PROV-02', 20),
('INV-12', 'SUC-02', 'PROD-06', 'PROV-03', 22),
('INV-13', 'SUC-03', 'PROD-06', 'PROV-02', 13),
('INV-14', 'SUC-03', 'PROD-05', 'PROV-02', 15),
('INV-15', 'SUC-01', 'PROD-07', 'PROV-03', 1),
('INV-16', 'SUC-02', 'PROD-07', 'PROV-02', 2),
('INV-17', 'SUC-03', 'PROD-07', 'PROV-01', 5);

-- ============================================================================
-- CONSULTA DE VERIFICACIÓN (SOLUCIÓN AL PROBLEMA DE LUPITA GARZA)
-- ============================================================================
SELECT 
    p.nombre_producto AS Producto,
    SUM(i.stock_actual) AS Total_Existencias,
    p.unidad_medida AS Unidad
FROM inventario i
INNER JOIN productos p ON i.id_producto = p.id_producto
WHERE p.id_producto = 'PROD-07'
GROUP BY p.nombre_producto, p.unidad_medida;