-- ============================================================
-- SISTEMA DE ANÁLISIS DE PROCUREMENT - FASE 2
-- Creación de esquema normalizado (3NF)
-- ============================================================

USE procurement_analytics;

-- ============================================================
-- 1. TABLAS CATÁLOGO
-- ============================================================

CREATE TABLE IF NOT EXISTS subsidiarias (
    id_subsidiaria INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL UNIQUE,
    activa BOOLEAN DEFAULT TRUE,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS usuarios (
    id_usuario INT AUTO_INCREMENT PRIMARY KEY,
    nombre_completo VARCHAR(200) NOT NULL UNIQUE,
    activo BOOLEAN DEFAULT TRUE,
    INDEX idx_nombre (nombre_completo)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS proveedores (
    id_proveedor INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL UNIQUE,
    activo BOOLEAN DEFAULT TRUE,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS familias (
    id_familia INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL UNIQUE,
    activa BOOLEAN DEFAULT TRUE,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ubicaciones (
    id_ubicacion INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL UNIQUE,
    activa BOOLEAN DEFAULT TRUE,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB;

-- ============================================================
-- 2. TABLAS TRANSACCIONALES
-- ============================================================

CREATE TABLE IF NOT EXISTS requisiciones (
    id_requisicion INT AUTO_INCREMENT PRIMARY KEY,
    folio_requisicion VARCHAR(50) NOT NULL,
    id_subsidiaria INT NOT NULL,
    id_solicitante INT DEFAULT NULL,
    id_autorizador_req INT DEFAULT NULL,
    area_consumo VARCHAR(200) DEFAULT NULL,
    id_ubicacion_consumo INT DEFAULT NULL,
    id_ubicacion_almacen INT DEFAULT NULL,
    id_ubicacion_recepcion INT DEFAULT NULL,
    comentarios TEXT DEFAULT NULL,
    
    fecha_creacion DATETIME DEFAULT NULL,
    fecha_envio_autorizacion DATETIME DEFAULT NULL,
    fecha_autorizacion DATETIME DEFAULT NULL,
    
    estatus_requisicion VARCHAR(50) DEFAULT NULL,
    
    INDEX idx_folio (folio_requisicion),
    INDEX idx_fechas (fecha_creacion, fecha_autorizacion),
    FOREIGN KEY (id_subsidiaria) REFERENCES subsidiarias(id_subsidiaria),
    FOREIGN KEY (id_solicitante) REFERENCES usuarios(id_usuario),
    FOREIGN KEY (id_autorizador_req) REFERENCES usuarios(id_usuario),
    FOREIGN KEY (id_ubicacion_consumo) REFERENCES ubicaciones(id_ubicacion),
    FOREIGN KEY (id_ubicacion_almacen) REFERENCES ubicaciones(id_ubicacion),
    FOREIGN KEY (id_ubicacion_recepcion) REFERENCES ubicaciones(id_ubicacion)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cotizaciones (
    id_cotizacion INT AUTO_INCREMENT PRIMARY KEY,
    folio_cotizacion VARCHAR(50) DEFAULT NULL,
    id_requisicion INT NOT NULL,
    
    fecha_invitacion_proveedor DATETIME DEFAULT NULL,
    fecha_finalizacion_cotizacion DATETIME DEFAULT NULL,
    estatus_cotizacion VARCHAR(50) DEFAULT NULL,
    
    INDEX idx_folio (folio_cotizacion),
    INDEX idx_requisicion (id_requisicion),
    FOREIGN KEY (id_requisicion) REFERENCES requisiciones(id_requisicion)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ordenes_compra (
    id_oc INT AUTO_INCREMENT PRIMARY KEY,
    folio_oc VARCHAR(50) DEFAULT NULL,
    id_requisicion INT NOT NULL,
    id_cotizacion INT DEFAULT NULL,
    id_comprador INT DEFAULT NULL,
    id_autorizador_oc INT DEFAULT NULL,
    
    fecha_envio_autorizacion_oc DATETIME DEFAULT NULL,
    fecha_autorizacion_oc DATETIME DEFAULT NULL,
    fecha_recepcion DATETIME DEFAULT NULL,
    
    estatus_orden_compra VARCHAR(50) DEFAULT NULL,
    estatus_almacen VARCHAR(50) DEFAULT NULL,
    
    INDEX idx_folio (folio_oc),
    INDEX idx_fechas (fecha_autorizacion_oc, fecha_recepcion),
    FOREIGN KEY (id_requisicion) REFERENCES requisiciones(id_requisicion),
    FOREIGN KEY (id_cotizacion) REFERENCES cotizaciones(id_cotizacion),
    FOREIGN KEY (id_comprador) REFERENCES usuarios(id_usuario),
    FOREIGN KEY (id_autorizador_oc) REFERENCES usuarios(id_usuario)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lineas_detalle (
    id_linea INT AUTO_INCREMENT PRIMARY KEY,
    id_interno_linea INT NOT NULL,
    id_oc INT DEFAULT NULL,
    id_requisicion INT NOT NULL,
    id_familia INT DEFAULT NULL,
    id_proveedor INT DEFAULT NULL,
    
    articulo VARCHAR(500) DEFAULT NULL,
    unidad VARCHAR(50) DEFAULT NULL,
    cantidad_oc DECIMAL(15,2) DEFAULT 0,
    cantidad_solicitada DECIMAL(15,2) DEFAULT 0,
    
    precio_ultima_compra DECIMAL(15,2) DEFAULT 0,
    precio_final_mxn DECIMAL(15,2) DEFAULT 0,
    moneda VARCHAR(10) DEFAULT 'MXN',
    precio_final_proveedor DECIMAL(15,2) DEFAULT 0,
    monto_total_colocado DECIMAL(15,2) DEFAULT 0,
    
    por_var_ultimo_precio DECIMAL(10,4) DEFAULT 0,
    por_var_precio_max DECIMAL(10,4) DEFAULT 0,
    por_var_precio_min DECIMAL(10,4) DEFAULT 0,
    
    proveedor_max VARCHAR(200) DEFAULT NULL,
    precio_final_max DECIMAL(15,2) DEFAULT 0,
    proveedor_min VARCHAR(200) DEFAULT NULL,
    precio_final_min DECIMAL(15,2) DEFAULT 0,
    
    INDEX idx_interno (id_interno_linea),
    FOREIGN KEY (id_oc) REFERENCES ordenes_compra(id_oc),
    FOREIGN KEY (id_requisicion) REFERENCES requisiciones(id_requisicion),
    FOREIGN KEY (id_familia) REFERENCES familias(id_familia),
    FOREIGN KEY (id_proveedor) REFERENCES proveedores(id_proveedor)
) ENGINE=InnoDB;

-- ============================================================
-- 3. VISTA MAESTRA (para compatibilidad con el dashboard)
-- ============================================================

CREATE OR REPLACE VIEW v_proceso_completo AS
SELECT
    l.id_interno_linea,
    s.nombre AS subsidiaria,
    r.folio_requisicion,
    c.folio_cotizacion AS cotizacion,
    o.folio_oc AS folio_orden_compra,
    u_sol.nombre_completo AS solicitante,
    u_auth_req.nombre_completo AS autoriza_req,
    u_comp.nombre_completo AS comprador,
    u_auth_oc.nombre_completo AS autorizador_oc,
    r.comentarios,
    r.area_consumo,
    ub_cons.nombre AS ubicacion_consumo,
    ub_alm.nombre AS ubicacion_almacen,
    ub_rec.nombre AS ubicacion_recepcion,
    
    r.fecha_creacion,
    r.fecha_envio_autorizacion,
    r.fecha_autorizacion,
    c.fecha_invitacion_proveedor,
    c.fecha_finalizacion_cotizacion,
    o.fecha_envio_autorizacion_oc,
    o.fecha_autorizacion_oc,
    o.fecha_recepcion,
    
    r.estatus_requisicion,
    o.estatus_almacen,
    c.estatus_cotizacion,
    o.estatus_orden_compra,
    
    l.articulo,
    l.unidad,
    l.cantidad_oc,
    l.cantidad_solicitada,
    l.precio_ultima_compra,
    f.nombre AS familia,
    p.nombre AS proveedor,
    l.precio_final_mxn,
    l.moneda,
    l.precio_final_proveedor,
    l.monto_total_colocado,
    l.por_var_ultimo_precio,
    l.por_var_precio_max,
    l.por_var_precio_min,
    l.proveedor_max,
    l.precio_final_max,
    l.proveedor_min,
    l.precio_final_min

FROM lineas_detalle l
LEFT JOIN requisiciones r ON l.id_requisicion = r.id_requisicion
LEFT JOIN ordenes_compra o ON l.id_oc = o.id_oc
LEFT JOIN cotizaciones c ON c.id_requisicion = l.id_requisicion AND (o.id_cotizacion IS NULL OR o.id_cotizacion = c.id_cotizacion)
LEFT JOIN subsidiarias s ON r.id_subsidiaria = s.id_subsidiaria
LEFT JOIN familias f ON l.id_familia = f.id_familia
LEFT JOIN proveedores p ON l.id_proveedor = p.id_proveedor
LEFT JOIN usuarios u_sol ON r.id_solicitante = u_sol.id_usuario
LEFT JOIN usuarios u_auth_req ON r.id_autorizador_req = u_auth_req.id_usuario
LEFT JOIN usuarios u_comp ON o.id_comprador = u_comp.id_usuario
LEFT JOIN usuarios u_auth_oc ON o.id_autorizador_oc = u_auth_oc.id_usuario
LEFT JOIN ubicaciones ub_cons ON r.id_ubicacion_consumo = ub_cons.id_ubicacion
LEFT JOIN ubicaciones ub_alm ON r.id_ubicacion_almacen = ub_alm.id_ubicacion
LEFT JOIN ubicaciones ub_rec ON r.id_ubicacion_recepcion = ub_rec.id_ubicacion;