-- Tabla: bitacoradepersonal
PRIMARY KEY (
    sContrato,           -- VARCHAR(15)
    dIdFecha,           -- DATE
    iIdDiario,          -- INT
    sIdPersonal,        -- VARCHAR(25)
    sIdPernocta,        -- VARCHAR(10)
    sIdPlataforma,      -- VARCHAR(50)
    sWbs,               -- VARCHAR(100)
    sNumeroActividad,   -- VARCHAR(20)
    sNumeroOrden,       -- VARCHAR(35)
    Item,               -- INT
    sAnexo,             -- VARCHAR(10)
    sClasificacionSitio -- ENUM
)

CREATE TABLE anexo_requisicion (
    sStatus ENUM(
        'PENDIENTE',
        'VALIDADO',
        'COMPRADO',
        'LIBERADO',
        'RECHAZADO',
        'CANCELADO',
        'ASIGNADO',
        'GENERO PR',
        'AUTORIZADO'
    )
)

UsuarioValido VARCHAR(40),
FechaValido DATETIME,
UsuarioAutorizo VARCHAR(40),
FechaAutorizo DATETIME

-- Tabla: actividadesxanexo
UPDATE actividadesxanexo 
SET dVentaMN = 1500.00  -- Precio original: 1200.00 (PERDIDO)
WHERE sNumeroActividad = 'ACT-001'

-- Cantidades en diferentes tablas:
dCantidad DOUBLE(13,5)      -- 28 tablas
dCantidad DECIMAL(12,4)     -- 19 tablas
dCantidad DECIMAL(17,4)     -- 8 tablas
dCantidad FLOAT(9,3)        -- 4 tablas

Día 1: INSERT bitacoradeactividades (dCantidad=10)
       UPDATE actividadesxorden SET dInstalado=10

Día 2: INSERT bitacoradeactividades (dCantidad=15)
       UPDATE bitacoradeactividades SET dCantidadAnterior=10
       UPDATE actividadesxorden SET dInstalado=25

Corrección Día 1: 
       UPDATE bitacoradeactividades SET dCantidad=8
       -- Valor original 10 SE PIERDE PERMANENTEMENTE

CREATE TABLE bitacoradeactividades (
    iIdActividad INT,
    sCantidadDetalle MEDIUMTEXT  -- "10.5,15.3,20.1"
)

CREATE TABLE daily_activity_progress (
    id BIGINT PRIMARY KEY,
    daily_report_id BIGINT
)

CREATE TABLE daily_activity_progress_detail (
    id BIGINT PRIMARY KEY,
    progress_id BIGINT REFERENCES daily_activity_progress(id),
    detail_value DECIMAL(18,5),
    detail_sequence INT
)

CREATE TABLE actividadesxanexo (
    sContrato VARCHAR(15),
    sNumeroActividad VARCHAR(20),
    mDescripcion MEDIUMTEXT,
    dVentaMN DOUBLE,
    PRIMARY KEY (sContrato, sNumeroActividad)
)

-- Catálogo de actividades (descripción depende solo de actividad)
CREATE TABLE project_activities_catalog (
    id BIGINT PRIMARY KEY,
    project_id INT REFERENCES projects(id),
    code VARCHAR(50),
    description TEXT
)

-- Precios por actividad (separado y versionado)
CREATE TABLE project_activity_prices (
    id BIGINT PRIMARY KEY,
    activity_id BIGINT REFERENCES project_activities_catalog(id),
    currency_id INT REFERENCES cat_currencies(id),
    sale_price DECIMAL(18,2),
    effective_date DATE,
    end_date DATE
)

CREATE TABLE anexo_ppedido (
    IdOrdenCompra INT,
    IdInsumo INT,
    NombreInsumo VARCHAR(200),  -- Depende de IdInsumo
    FamiliaInsumo VARCHAR(100), -- Depende de IdInsumo
    PRIMARY KEY (IdOrdenCompra, IdInsumo)
)

CREATE TABLE purchase_order_details (
    id BIGINT PRIMARY KEY,
    po_id INT REFERENCES purchase_orders(id),
    product_id INT REFERENCES products(id)
    -- Nombre y familia se obtienen por JOIN con products
)

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    family_id INT REFERENCES product_families(id)
)

CREATE TABLE bitacoradeactividades (
    PRIMARY KEY (
        sContrato VARCHAR(15),
        dIdFecha DATE,
        iIdDiario INT,
        sNumeroOrden VARCHAR(35)
    )
)

-- Análisis de tamaño:
-- Tamaño de clave primaria: 15 + 3 + 4 + 35 = 57 bytes
-- Tamaño de índice FK: 57 bytes por referencia
-- Registros: 2,450,000
-- Tamaño total de índices PK: 139.65 MB

SELECT * 
FROM bitacoradeactividades ba
JOIN actividadesxorden ao ON 
    ba.sContrato = ao.sContrato AND
    ba.sNumeroOrden = ao.sNumeroOrden AND
    ba.sNumeroActividad = ao.sNumeroActividad

-- Tiempo de ejecución: 1,850 ms
-- Filas examinadas: 845,000
-- Uso de índice: Parcial (solo 2 de 4 campos)

CREATE TABLE daily_activity_progress (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    daily_report_id BIGINT NOT NULL,
    work_order_activity_id BIGINT NOT NULL,
    -- Campos de negocio...
    
    FOREIGN KEY (daily_report_id) 
        REFERENCES daily_reports(id),
    FOREIGN KEY (work_order_activity_id) 
        REFERENCES work_order_activities(id)
)

-- Análisis de tamaño:
-- Tamaño de clave primaria: 8 bytes
-- Tamaño de índice FK: 8 bytes por referencia
-- Registros: 2,450,000
-- Tamaño total de índices PK: 19.53 MB

SELECT * 
FROM daily_activity_progress dap
JOIN work_order_activities woa ON dap.work_order_activity_id = woa.id

-- Tiempo de ejecución: 185 ms (90% mejora)
-- Filas examinadas: 2,450 (99.7% reducción)
-- Uso de índice: Completo

CREATE TABLE sys_audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    record_id VARCHAR(100) NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    field_name VARCHAR(64),
    old_value TEXT,
    new_value TEXT,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_created_at (created_at),
    INDEX idx_user (user_id)
)

DELIMITER $$

CREATE TRIGGER trg_audit_projects_update
AFTER UPDATE ON projects
FOR EACH ROW
BEGIN
    IF OLD.contract_amount_mxn != NEW.contract_amount_mxn THEN
        INSERT INTO sys_audit_log (
            table_name, 
            record_id, 
            action, 
            field_name, 
            old_value, 
            new_value, 
            user_id
        ) VALUES (
            'projects',
            NEW.id,
            'UPDATE',
            'contract_amount_mxn',
            OLD.contract_amount_mxn,
            NEW.contract_amount_mxn,
            @current_user_id
        );
    END IF;
    
    IF OLD.status != NEW.status THEN
        INSERT INTO sys_audit_log (
            table_name, 
            record_id, 
            action, 
            field_name, 
            old_value, 
            new_value, 
            user_id
        ) VALUES (
            'projects',
            NEW.id,
            'UPDATE',
            'status',
            OLD.status,
            NEW.status,
            @current_user_id
        );
    END IF;
END$$

DELIMITER ;

CREATE TABLE workflow_state_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('REQUISITION', 'PURCHASE_ORDER', 'WAREHOUSE_MOVEMENT'),
    entity_id INT NOT NULL,
    from_state_id INT,
    to_state_id INT NOT NULL,
    changed_by_user_id INT NOT NULL,
    comments TEXT,
    changed_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    FOREIGN KEY (from_state_id) REFERENCES cat_workflow_states(id),
    FOREIGN KEY (to_state_id) REFERENCES cat_workflow_states(id),
    FOREIGN KEY (changed_by_user_id) REFERENCES sys_users(id)
)

-- Consulta: ¿Quién cambió el precio de la actividad ACT-001?
SELECT 
    a.created_at AS fecha_cambio,
    u.full_name AS usuario,
    a.old_value AS precio_anterior,
    a.new_value AS precio_nuevo
FROM sys_audit_log a
JOIN sys_users u ON a.user_id = u.id
WHERE a.table_name = 'project_activity_prices'
    AND a.record_id = '1523'
    AND a.field_name = 'sale_price'
ORDER BY a.created_at DESC

-- Resultado:
-- 2024-11-15 14:32:18 | Juan Pérez | 1200.00 | 1500.00
-- 2024-10-01 09:15:43 | María López | 1000.00 | 1200.00
-- 2024-08-12 11:20:12 | Juan Pérez | 950.00 | 1000.00

-- Consulta: ¿Cuál fue el flujo de aprobación de la requisición REQ-2024-001?
SELECT 
    h.changed_at AS fecha,
    u.full_name AS usuario,
    ws_from.name AS estado_anterior,
    ws_to.name AS estado_nuevo,
    h.comments AS comentarios
FROM workflow_state_history h
JOIN sys_users u ON h.changed_by_user_id = u.id
LEFT JOIN cat_workflow_states ws_from ON h.from_state_id = ws_from.id
JOIN cat_workflow_states ws_to ON h.to_state_id = ws_to.id
WHERE h.entity_type = 'REQUISITION'
    AND h.entity_id = 1847
ORDER BY h.changed_at

-- Resultado:
-- 2024-11-10 08:30:00 | Ana García | NULL | Borrador
-- 2024-11-10 09:15:23 | Ana García | Borrador | Enviada
-- 2024-11-10 14:45:12 | Luis Martínez | Enviada | Rechazada | Falta especificación
-- 2024-11-11 10:20:05 | Ana García | Rechazada | Enviada
-- 2024-11-11 15:30:48 | Luis Martínez | Enviada | Aprobada

CREATE TABLE project_activities_catalog (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    code VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    version INT DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    UNIQUE KEY uk_project_code_version (project_id, code, version)
)

CREATE TABLE project_activity_prices (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT NOT NULL,
    currency_id INT NOT NULL,
    sale_price DECIMAL(18,2) NOT NULL,
    effective_date DATE NOT NULL,
    end_date DATE,
    price_adjustment_reason VARCHAR(200),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    FOREIGN KEY (activity_id) REFERENCES project_activities_catalog(id),
    FOREIGN KEY (currency_id) REFERENCES cat_currencies(id),
    
    INDEX idx_activity_date (activity_id, effective_date)
)

-- Función para obtener precio vigente en una fecha específica
CREATE FUNCTION get_activity_price(
    p_activity_id BIGINT,
    p_currency_code VARCHAR(3),
    p_date DATE
) RETURNS DECIMAL(18,2)
READS SQL DATA
BEGIN
    DECLARE v_price DECIMAL(18,2);
    
    SELECT sale_price INTO v_price
    FROM project_activity_prices pap
    JOIN cat_currencies c ON pap.currency_id = c.id
    WHERE pap.activity_id = p_activity_id
        AND c.code = p_currency_code
        AND pap.effective_date <= p_date
        AND (pap.end_date IS NULL OR pap.end_date >= p_date)
    ORDER BY pap.effective_date DESC
    LIMIT 1;
    
    RETURN v_price;
END;

-- Uso:
SELECT get_activity_price(1523, 'MXN', '2024-10-15')
-- Retorna: 1200.00

CREATE TABLE cat_currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(3) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    symbol VARCHAR(10),
    is_active BOOLEAN DEFAULT TRUE
)

-- Datos:
INSERT INTO cat_currencies (code, name, symbol) VALUES
('MXN', 'Peso Mexicano', '$'),
('USD', 'Dólar Estadounidense', 'US$'),
('EUR', 'Euro', '€'),
('CAD', 'Dólar Canadiense', 'C$')

CREATE TABLE cat_exchange_rates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    from_currency_id INT NOT NULL,
    to_currency_id INT NOT NULL,
    rate DECIMAL(18, 6) NOT NULL,
    effective_date DATE NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    FOREIGN KEY (from_currency_id) REFERENCES cat_currencies(id),
    FOREIGN KEY (to_currency_id) REFERENCES cat_currencies(id),
    
    UNIQUE KEY uk_currencies_date (
        from_currency_id, 
        to_currency_id, 
        effective_date
    )
)

-- Ejemplo de datos:
INSERT INTO cat_exchange_rates 
    (from_currency_id, to_currency_id, rate, effective_date)
VALUES
    (2, 1, 17.2500, '2024-11-01'),  -- USD a MXN
    (2, 1, 17.3200, '2024-11-02'),
    (2, 1, 17.2800, '2024-11-03')

-- Ahora solo se almacena un monto con su moneda
CREATE TABLE purchase_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    currency_id INT NOT NULL,
    exchange_rate DECIMAL(18, 6) DEFAULT 1.000000,
    total_amount DECIMAL(18, 2) NOT NULL,
    
    FOREIGN KEY (currency_id) REFERENCES cat_currencies(id)
)

CREATE FUNCTION convert_currency(
    p_amount DECIMAL(18,2),
    p_from_currency_code VARCHAR(3),
    p_to_currency_code VARCHAR(3),
    p_date DATE
) RETURNS DECIMAL(18,2)
READS SQL DATA
BEGIN
    DECLARE v_rate DECIMAL(18,6);
    
    IF p_from_currency_code = p_to_currency_code THEN
        RETURN p_amount;
    END IF;
    
    SELECT rate INTO v_rate
    FROM cat_exchange_rates er
    JOIN cat_currencies cf ON er.from_currency_id = cf.id
    JOIN cat_currencies ct ON er.to_currency_id = ct.id
    WHERE cf.code = p_from_currency_code
        AND ct.code = p_to_currency_code
        AND er.effective_date <= p_date
    ORDER BY er.effective_date DESC
    LIMIT 1;
    
    RETURN p_amount * v_rate;
END;

CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    -- campos de negocio...
    
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP(6) NULL,
    deleted_by_user_id INT,
    
    FOREIGN KEY (deleted_by_user_id) REFERENCES sys_users(id),
    
    INDEX idx_is_deleted (is_deleted)
)

DELIMITER $$

CREATE TRIGGER trg_soft_delete_projects
BEFORE DELETE ON projects
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Eliminación física no permitida. Use soft delete.';
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_soft_delete_project(
    IN p_project_id INT,
    IN p_user_id INT
)
BEGIN
    UPDATE projects
    SET 
        is_deleted = TRUE,
        deleted_at = CURRENT_TIMESTAMP(6),
        deleted_by_user_id = p_user_id
    WHERE id = p_project_id
        AND is_deleted = FALSE;
        
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Proyecto no encontrado o ya eliminado.';
    END IF;
END$$

DELIMITER ;

-- Todas las tablas incluyen:
created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) 
    ON UPDATE CURRENT_TIMESTAMP(6)

-- Identificar el último cambio con precisión de microsegundos
SELECT *
FROM sys_audit_log
WHERE table_name = 'projects'
    AND record_id = '123'
ORDER BY created_at DESC
LIMIT 1

-- Resultado:
-- created_at: 2024-11-15 14:32:18.847392
-- vs
-- created_at: 2024-11-15 14:32:18.847385
-- Diferencia: 7 microsegundos

CREATE INDEX idx_status ON projects(status);
CREATE INDEX idx_is_deleted ON projects(is_deleted);
CREATE INDEX idx_report_date ON daily_reports(report_date);

-- Para: WHERE client_id = X AND status = Y AND is_deleted = FALSE
CREATE INDEX idx_projects_client_status 
    ON projects(client_id, status, is_deleted);

-- Para: WHERE work_order_id = X AND report_date BETWEEN Y AND Z
CREATE INDEX idx_daily_reports_wo_date 
    ON daily_reports(work_order_id, report_date);

-- Para: WHERE movement_type = X AND movement_date BETWEEN Y AND Z
CREATE INDEX idx_inventory_movements_type_date 
    ON inventory_movements(movement_type, movement_date);

CREATE INDEX idx_work_order 
    ON daily_activity_progress(work_order_activity_id);
    
CREATE INDEX idx_product 
    ON inventory_stock(product_id);

CREATE VIEW v_inventory_available AS
SELECT 
    s.id,
    w.code AS warehouse_code,
    w.name AS warehouse_name,
    p.code AS product_code,
    p.name AS product_name,
    pf.name AS family_name,
    u.code AS unit_code,
    s.quantity,
    s.reserved_quantity,
    s.available_quantity,
    s.last_movement_date,
    CASE 
        WHEN s.available_quantity <= p.min_stock THEN 'BAJO'
        WHEN s.available_quantity >= p.max_stock THEN 'ALTO'
        ELSE 'NORMAL'
    END AS stock_status
FROM inventory_stock s
JOIN warehouses w ON s.warehouse_id = w.id
JOIN products p ON s.product_id = p.id
JOIN product_families pf ON p.family_id = pf.id
JOIN cat_units u ON p.unit_id = u.id
WHERE w.is_active = TRUE 
    AND p.is_active = TRUE;

-- En lugar de 5 JOINs cada vez, una sola consulta a la vista
SELECT * 
FROM v_inventory_available
WHERE warehouse_code = 'ALM-001'
    AND stock_status = 'BAJO'
ORDER BY family_name, product_name;

CREATE VIEW v_active_projects AS
SELECT 
    p.id,
    p.code,
    p.name,
    c.business_name AS client_name,
    u.full_name AS project_manager,
    p.start_date,
    p.end_date,
    p.contract_amount_mxn,
    p.contract_amount_usd,
    p.status,
    COUNT(DISTINCT wo.id) AS work_order_count,
    SUM(CASE WHEN wo.status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_orders,
    ROUND(
        SUM(CASE WHEN wo.status = 'COMPLETED' THEN 1 ELSE 0 END) * 100.0 / 
        COUNT(DISTINCT wo.id), 
        2
    ) AS completion_percentage
FROM projects p
JOIN cat_clients c ON p.client_id = c.id
LEFT JOIN sys_users u ON p.project_manager_user_id = u.id
LEFT JOIN work_orders wo ON p.id = wo.project_id AND wo.is_deleted = FALSE
WHERE p.is_deleted = FALSE 
    AND p.status IN ('ACTIVE', 'DRAFT')
GROUP BY p.id;

CREATE TABLE daily_activity_progress (
    id BIGINT AUTO_INCREMENT,
    daily_report_id BIGINT NOT NULL,
    work_order_activity_id BIGINT NOT NULL,
    report_date DATE NOT NULL,
    quantity_executed DECIMAL(18, 5) DEFAULT 0,
    progress_percentage DECIMAL(8, 4) DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    
    PRIMARY KEY (id, report_date)
)
PARTITION BY RANGE (YEAR(report_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Consulta que solo accede a partición 2024
SELECT *
FROM daily_activity_progress
WHERE report_date BETWEEN '2024-01-01' AND '2024-12-31'

-- Explicación del query:
-- partitions: p2024 (solo 1 de 5 particiones)
-- rows examined: 145,823 (vs 2,450,000 sin particionamiento)
-- execution time: 89 ms (vs 1,240 ms)

-- Archivar datos antiguos (operación instantánea)
ALTER TABLE daily_activity_progress 
DROP PARTITION p2022;

-- vs eliminación tradicional:
DELETE FROM daily_activity_progress 
WHERE YEAR(report_date) = 2022;
-- Tiempo: 4.5 horas, bloqueos de tabla, log de transacciones lleno

CREATE TABLE inventory_stock (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(18, 5) DEFAULT 0,
    reserved_quantity DECIMAL(18, 5) DEFAULT 0,
    
    -- Campo calculado automáticamente
    available_quantity DECIMAL(18, 5) AS (quantity - reserved_quantity) STORED,
    
    INDEX idx_available (available_quantity)
)

-- ANTES: Cálculo manual propenso a errores
UPDATE inventory_stock 
SET available_quantity = quantity - reserved_quantity
WHERE product_id = 123;

-- Error común: olvidar actualizar available_quantity

-- DESPUÉS: Actualización automática
UPDATE inventory_stock 
SET reserved_quantity = 50
WHERE product_id = 123;
-- available_quantity se recalcula automáticamente

-- Consulta con campo calculado (indexado)
SELECT * 
FROM inventory_stock
WHERE available_quantity < 10

-- Execution plan:
-- type: range
-- key: idx_available
-- rows: 127
-- execution time: 3 ms

-- vs consulta con cálculo inline:
SELECT * 
FROM inventory_stock
WHERE (quantity - reserved_quantity) < 10

-- Execution plan:
-- type: ALL (full table scan)
-- rows: 45,823
-- execution time: 340 ms

SELECT 
    a.sNumeroActividad,
    a.mDescripcion,
    SUM(b.dCantidad) AS total_ejecutado
FROM actividadesxanexo a
LEFT JOIN bitacoradeactividades b ON 
    a.sContrato = b.sContrato AND
    a.sNumeroActividad = b.sNumeroActividad
WHERE a.sContrato = 'CONT-2024-001'
    AND b.dIdFecha BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY a.sNumeroActividad

-- EXPLAIN:
-- +----+-------------+-------+------+--------+---------+-------------+
-- | id | select_type | table | type | key    | rows    | Extra       |
-- +----+-------------+-------+------+--------+---------+-------------+
-- |  1 | SIMPLE      | a     | ALL  | NULL   | 12,847  | Using where |
-- |  1 | SIMPLE      | b     | ALL  | NULL   | 845,234 | Using where |
-- +----+-------------+-------+------+--------+---------+-------------+
-- Execution time: 8,450 ms

SELECT 
    ac.code AS activity_code,
    ac.description,
    SUM(dap.quantity_executed) AS total_executed
FROM project_activities_catalog ac
LEFT JOIN work_order_activities woa ON ac.id = woa.activity_catalog_id
LEFT JOIN daily_activity_progress dap ON woa.id = dap.work_order_activity_id
LEFT JOIN daily_reports dr ON dap.daily_report_id = dr.id
WHERE ac.project_id = 123
    AND dr.report_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY ac.id

-- EXPLAIN:
-- +----+-------------+-------+-------+----------------------+------+-----------+
-- | id | select_type | table | type  | key                  | rows | Extra     |
-- +----+-------------+-------+-------+----------------------+------+-----------+
-- |  1 | SIMPLE      | ac    | ref   | idx_project          | 847  | Using idx |
-- |  1 | SIMPLE      | woa   | ref   | idx_activity_catalog | 1    |           |
-- |  1 | SIMPLE      | dap   | ref   | idx_wo_activity      | 145  |           |
-- |  1 | SIMPLE      | dr    | range | idx_report_date      | 365  | Using idx |
-- +----+-------------+-------+-------+----------------------+------+-----------+
-- Execution time: 142 ms

-- 1. Migrar unidades de medida
INSERT INTO cat_units (code, name, is_active)
SELECT DISTINCT 
    IdMedida AS code,
    Descripcion AS name,
    TRUE
FROM master_medidas
WHERE Activo = 'Si';

-- Validación:
SELECT 
    (SELECT COUNT(*) FROM master_medidas WHERE Activo = 'Si') AS original,
    (SELECT COUNT(*) FROM cat_units WHERE is_active = TRUE) AS migrado,
    CASE 
        WHEN COUNT(*) = (SELECT COUNT(*) FROM master_medidas WHERE Activo = 'Si')
        THEN 'OK' 
        ELSE 'ERROR' 
    END AS status
FROM cat_units;

-- 2. Migrar proveedores
INSERT INTO cat_suppliers (
    code, 
    business_name, 
    tax_id, 
    email, 
    phone, 
    is_active
)
SELECT 
    IdProveedor AS code,
    RazonSocial AS business_name,
    RFC AS tax_id,
    Email AS email,
    Telefono AS phone,
    CASE Activo WHEN 'Si' THEN TRUE ELSE FALSE END
FROM master_proveedores;

-- 3. Migrar clientes
INSERT INTO cat_clients (
    code,
    business_name,
    tax_id,
    is_active
)
SELECT 
    IdCliente AS code,
    RazonSocial AS business_name,
    RFC AS tax_id,
    CASE Activo WHEN 'Si' THEN TRUE ELSE FALSE END
FROM master_clientes;

-- Procedimiento de validación
DELIMITER $$

CREATE PROCEDURE sp_validate_catalog_migration()
BEGIN
    DECLARE v_errors INT DEFAULT 0;
    
    -- Validar unidades
    IF (SELECT COUNT(*) FROM master_medidas WHERE Activo = 'Si') != 
       (SELECT COUNT(*) FROM cat_units WHERE is_active = TRUE) THEN
        SET v_errors = v_errors + 1;
        SELECT 'ERROR: Unidades no coinciden' AS error_message;
    END IF;
    
    -- Validar proveedores
    IF (SELECT COUNT(*) FROM master_proveedores WHERE Activo = 'Si') != 
       (SELECT COUNT(*) FROM cat_suppliers WHERE is_active = TRUE) THEN
        SET v_errors = v_errors + 1;
        SELECT 'ERROR: Proveedores no coinciden' AS error_message;
    END IF;
    
    IF v_errors = 0 THEN
        SELECT 'ÉXITO: Todos los catálogos migrados correctamente' AS status;
    END IF;
END$$

DELIMITER ;

-- Crear mapeo de IDs
CREATE TEMPORARY TABLE temp_project_mapping (
    old_code VARCHAR(15),
    new_id INT,
    INDEX (old_code)
);

-- Migrar proyectos
INSERT INTO projects (
    code,
    name,
    client_id,
    start_date,
    end_date,
    contract_amount_mxn,
    contract_amount_usd,
    status
)
SELECT 
    p.sContrato,
    p.sDescripcion,
    c.id,
    p.dFechaInicio,
    p.dFechaFinal,
    p.dMontoMN,
    p.dMontoDLL,
    CASE p.sEstatus
        WHEN 'ACTIVO' THEN 'ACTIVE'
        WHEN 'TERMINADO' THEN 'COMPLETED'
        WHEN 'CANCELADO' THEN 'CANCELLED'
        ELSE 'DRAFT'
    END
FROM rd_proyectos p
JOIN cat_clients c ON p.IdCliente = c.code;

-- Guardar mapeo
INSERT INTO temp_project_mapping (old_code, new_id)
SELECT code, id FROM projects;

-- Migrar órdenes de trabajo
INSERT INTO work_orders (
    project_id,
    code,
    name,
    planned_start_date,
    planned_end_date,
    status
)
SELECT 
    pm.new_id,
    ot.sNumeroOrden,
    ot.sDescripcion,
    ot.dFechaInicio,
    ot.dFechaFinal,
    CASE ot.sEstatus
        WHEN 'EN PROCESO' THEN 'IN_PROGRESS'
        WHEN 'TERMINADA' THEN 'COMPLETED'
        ELSE 'PLANNED'
    END
FROM ordenesdetrabajo ot
JOIN temp_project_mapping pm ON ot.sContrato = pm.old_code;

-- Verificar que todas las órdenes tienen proyecto válido
SELECT 
    COUNT(*) AS ordenes_huerfanas
FROM ordenesdetrabajo ot
LEFT JOIN temp_project_mapping pm ON ot.sContrato = pm.old_code
WHERE pm.new_id IS NULL;

-- Resultado esperado: 0

-- Paso 1: Migrar catálogo de actividades
INSERT INTO project_activities_catalog (
    project_id,
    code,
    wbs_code,
    description,
    unit_id,
    quantity,
    version
)
SELECT 
    pm.new_id,
    a.sNumeroActividad,
    a.sWbs,
    a.mDescripcion,
    u.id,
    a.dCantidadAnexo,
    1
FROM actividadesxanexo a
JOIN temp_project_mapping pm ON a.sContrato = pm.old_code
JOIN cat_units u ON a.IdMedida = u.code;

-- Paso 2: Crear mapeo de actividades
CREATE TEMPORARY TABLE temp_activity_mapping (
    old_contract VARCHAR(15),
    old_code VARCHAR(20),
    new_id BIGINT,
    INDEX (old_contract, old_code)
);

INSERT INTO temp_activity_mapping
SELECT 
    a.sContrato,
    a.sNumeroActividad,
    pac.id
FROM actividadesxanexo a
JOIN project_activities_catalog pac ON 
    pac.code = a.sNumeroActividad AND
    pac.project_id = (SELECT new_id FROM temp_project_mapping WHERE old_code = a.sContrato);

-- Paso 3: Migrar precios (MXN)
INSERT INTO project_activity_prices (
    activity_id,
    currency_id,
    cost_price,
    sale_price,
    effective_date
)
SELECT 
    am.new_id,
    (SELECT id FROM cat_currencies WHERE code = 'MXN'),
    a.dCostoMN,
    a.dVentaMN,
    p.start_date
FROM actividadesxanexo a
JOIN temp_activity_mapping am ON 
    a.sContrato = am.old_contract AND 
    a.sNumeroActividad = am.old_code
JOIN projects p ON p.id = (SELECT new_id FROM temp_project_mapping WHERE old_code = a.sContrato)
WHERE a.dVentaMN > 0;

-- Paso 4: Migrar precios (USD)
INSERT INTO project_activity_prices (
    activity_id,
    currency_id,
    cost_price,
    sale_price,
    effective_date
)
SELECT 
    am.new_id,
    (SELECT id FROM cat_currencies WHERE code = 'USD'),
    a.dCostoDLL,
    a.dVentaDLL,
    p.start_date
FROM actividadesxanexo a
JOIN temp_activity_mapping am ON 
    a.sContrato = am.old_contract AND 
    a.sNumeroActividad = am.old_code
JOIN projects p ON p.id = (SELECT new_id FROM temp_project_mapping WHERE old_code = a.sContrato)
WHERE a.dVentaDLL > 0;

-- Comparar totales antes y después
SELECT 
    'Legacy' AS source,
    COUNT(*) AS activities,
    SUM(dVentaMN) AS total_mxn,
    SUM(dVentaDLL) AS total_usd
FROM actividadesxanexo
UNION ALL
SELECT 
    'Refactorizada' AS source,
    COUNT(DISTINCT activity_id) AS activities,
    SUM(CASE WHEN c.code = 'MXN' THEN sale_price ELSE 0 END) AS total_mxn,
    SUM(CASE WHEN c.code = 'USD' THEN sale_price ELSE 0 END) AS total_usd
FROM project_activity_prices pap
JOIN cat_currencies c ON pap.currency_id = c.id;

-- Resultado esperado: diferencia < 0.01%

-- Procedimiento de migración por lotes
DELIMITER $$

CREATE PROCEDURE sp_migrate_daily_reports(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    DECLARE v_date DATE;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE date_cursor CURSOR FOR
        SELECT DISTINCT dIdFecha 
        FROM bitacoradeactividades
        WHERE dIdFecha BETWEEN p_start_date AND p_end_date
        ORDER BY dIdFecha;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    OPEN date_cursor;
    
    date_loop: LOOP
        FETCH date_cursor INTO v_date;
        
        IF v_done THEN
            LEAVE date_loop;
        END IF;
        
        -- Migrar reportes diarios
        INSERT INTO daily_reports (
            work_order_id,
            report_date,
            shift,
            status
        )
        SELECT DISTINCT
            wo.id,
            ba.dIdFecha,
            CASE ba.sIdTurno
                WHEN 'DI' THEN 'DAY'
                WHEN 'NO' THEN 'NIGHT'
                ELSE 'MIXED'
            END,
            'APPROVED'
        FROM bitacoradeactividades ba
        JOIN work_orders wo ON wo.code = ba.sNumeroOrden
        WHERE ba.dIdFecha = v_date
        ON DUPLICATE KEY UPDATE status = status;
        
        -- Migrar avances de actividades
        INSERT INTO daily_activity_progress (
            daily_report_id,
            work_order_activity_id,
            quantity_executed,
            progress_percentage,
            previous_accumulated,
            current_accumulated
        )
        SELECT 
            dr.id,
            woa.id,
            ba.dCantidad,
            ba.dAvance,
            ba.dCantidadAnterior,
            ba.dCantidadActual
        FROM bitacoradeactividades ba
        JOIN daily_reports dr ON 
            dr.report_date = ba.dIdFecha
        JOIN work_orders wo ON wo.code = ba.sNumeroOrden AND dr.work_order_id = wo.id
        JOIN work_order_activities woa ON 
            woa.work_order_id = wo.id AND
            woa.activity_catalog_id = (
                SELECT id FROM project_activities_catalog 
                WHERE code = ba.sNumeroActividad 
                AND project_id = wo.project_id
            )
        WHERE ba.dIdFecha = v_date;
        
        -- Log de progreso
        INSERT INTO migration_log (
            migration_date,
            batch_date,
            records_migrated,
            status
        ) VALUES (
            NOW(),
            v_date,
            ROW_COUNT(),
            'SUCCESS'
        );
        
        COMMIT;
        
    END LOOP;
    
    CLOSE date_cursor;
END$$

DELIMITER ;

-- Ejecutar migración por períodos
CALL sp_migrate_daily_reports('2022-01-01', '2022-12-31');
CALL sp_migrate_daily_reports('2023-01-01', '2023-12-31');
CALL sp_migrate_daily_reports('2024-01-01', '2024-12-31');

SELECT 
    batch_date,
    records_migrated,
    status,
    migration_date
FROM migration_log
ORDER BY batch_date;

-- 1. Validar totales de proyectos
SELECT 
    'Proyectos' AS entity,
    (SELECT COUNT(*) FROM rd_proyectos) AS legacy_count,
    (SELECT COUNT(*) FROM projects) AS new_count,
    CASE 
        WHEN (SELECT COUNT(*) FROM rd_proyectos) = (SELECT COUNT(*) FROM projects)
        THEN 'OK' 
        ELSE 'ERROR' 
    END AS status;

-- 2. Validar montos contractuales
SELECT 
    'Montos' AS entity,
    ROUND(SUM(dMontoMN), 2) AS legacy_total,
    ROUND(SUM(contract_amount_mxn), 2) AS new_total,
    ROUND(ABS(SUM(dMontoMN) - SUM(contract_amount_mxn)), 2) AS difference,
    CASE 
        WHEN ABS(SUM(dMontoMN) - SUM(contract_amount_mxn)) < 0.01
        THEN 'OK' 
        ELSE 'ERROR' 
    END AS status
FROM rd_proyectos p
JOIN projects pn ON p.sContrato = pn.code;

-- 3. Validar actividades
SELECT 
    'Actividades' AS entity,
    (SELECT COUNT(*) FROM actividadesxanexo) AS legacy_count,
    (SELECT COUNT(*) FROM project_activities_catalog) AS new_count;

-- 4. Validar reportes diarios
SELECT 
    'Reportes Diarios' AS entity,
    COUNT(DISTINCT CONCAT(dIdFecha, sNumeroOrden)) AS legacy_count,
    (SELECT COUNT(*) FROM daily_reports) AS new_count
FROM bitacoradeactividades;

-- 5. Validar integridad referencial
SELECT 
    'Integridad FK' AS entity,
    COUNT(*) AS orphaned_records
FROM work_orders wo
LEFT JOIN projects p ON wo.project_id = p.id
WHERE p.id IS NULL;

-- Backup antes de cada fase
CREATE DATABASE armando_backup_phase_N;

CREATE TABLE armando_backup_phase_N.table_name 
AS SELECT * FROM armando_new.table_name;

-- Procedimiento de rollback
DELIMITER $$

CREATE PROCEDURE sp_rollback_to_phase(
    IN p_phase_number INT
)
BEGIN
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE table_cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = CONCAT('armando_backup_phase_', p_phase_number);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    SET FOREIGN_KEY_CHECKS = 0;
    
    OPEN table_cursor;
    
    rollback_loop: LOOP
        FETCH table_cursor INTO v_table_name;
        
        IF v_done THEN
            LEAVE rollback_loop;
        END IF;
        
        SET @sql = CONCAT('DROP TABLE IF EXISTS armando_new.', v_table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET @sql = CONCAT(
            'CREATE TABLE armando_new.', v_table_name,
            ' AS SELECT * FROM armando_backup_phase_', p_phase_number, '.', v_table_name
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END LOOP;
    
    CLOSE table_cursor;
    
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

¿Refactorizar completo?
│
├─ SÍ, si:
│  ├─ Rendimiento degradado > 80%
│  ├─ Costo de mantenimiento > $50K USD/año
│  ├─ Pérdida de datos críticos documentada
│  └─ Escalabilidad bloqueada
│
└─ NO, considerar alternativas:
   ├─ Optimización incremental
   ├─ Modernización parcial
   └─ Migración a nuevo sistema

-- Query de monitoreo de rendimiento
CREATE VIEW v_performance_monitoring AS
SELECT 
    DATE(created_at) AS date,
    table_name,
    COUNT(*) AS query_count,
    AVG(execution_time_ms) AS avg_time,
    MAX(execution_time_ms) AS max_time,
    SUM(rows_examined) AS total_rows_examined
FROM query_log
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(created_at), table_name
ORDER BY avg_time DESC;