# FASE 3: Plan de Correcciones

**ID:** EPIC-VAL-003
**Fecha:** 2026-01-04
**Estado:** En Progreso
**Basado en:** FASE-2 (Analisis Consolidado)

---

## 1. Resumen del Plan

### 1.1 Alcance

| Metrica | Cantidad |
|---------|----------|
| Gaps P0 a resolver | 18 |
| Gaps P1 a resolver | 25 |
| Total correcciones planificadas | 43 |
| Archivos DDL a modificar | 8 |
| Nuevas tablas a crear | 28 |
| Nuevos campos a agregar | 85+ |
| Nuevas funciones a crear | 15 |

### 1.2 Criterios de Priorizacion

1. **P0 (Critico):** Bloquea funcionalidad core, sin workaround
2. **P1 (Alto):** Funcionalidad importante, workaround posible
3. **P2 (Medio):** Mejora significativa, no bloquea
4. **P3 (Bajo):** Nice-to-have, puede diferirse

---

## 2. Correcciones P0 (Criticas)

### 2.1 CORE - Estados/Provincias

**ID:** COR-021
**Gap:** GAP-CORE-TBL-001
**Archivo:** `02-core.sql`

```sql
-- Tabla: core.states
CREATE TABLE core.states (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    country_id UUID NOT NULL REFERENCES core.countries(id),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(tenant_id, country_id, code)
);

COMMENT ON TABLE core.states IS 'States/Provinces - Equivalent to res.country.state';
```

**Dependencias:** core.countries (existe)
**Impacto:** partners.state_id podra referenciar

---

### 2.2 CORE - Sistema Bancario

**ID:** COR-022
**Gap:** GAP-CORE-TBL-002, GAP-CORE-TBL-003
**Archivo:** `02-core.sql`

```sql
-- Tabla: core.banks
CREATE TABLE core.banks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    bic VARCHAR(11),  -- SWIFT/BIC code
    country_id UUID REFERENCES core.countries(id),
    street VARCHAR(255),
    city VARCHAR(100),
    zip VARCHAR(20),
    phone VARCHAR(50),
    email VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(tenant_id, bic)
);

-- Tabla: core.partner_banks
CREATE TABLE core.partner_banks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    partner_id UUID NOT NULL REFERENCES core.partners(id) ON DELETE CASCADE,
    bank_id UUID REFERENCES core.banks(id),
    acc_number VARCHAR(64) NOT NULL,
    acc_holder_name VARCHAR(255),
    sequence INTEGER DEFAULT 10,
    currency_id UUID REFERENCES core.currencies(id),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

COMMENT ON TABLE core.banks IS 'Banks catalog - Equivalent to res.bank';
COMMENT ON TABLE core.partner_banks IS 'Partner bank accounts - Equivalent to res.partner.bank';
```

**Dependencias:** core.partners, core.countries, core.currencies (existen)
**Impacto:** Pagos, conciliacion bancaria

---

### 2.3 FINANCIAL - Bank Statements

**ID:** COR-023
**Gap:** GAP-FIN-TBL-001, GAP-FIN-TBL-002
**Archivo:** `04-financial.sql`

```sql
-- ENUM para estado de extracto
CREATE TYPE financial.statement_status AS ENUM (
    'draft',
    'open',
    'confirm',
    'cancelled'
);

-- Tabla: financial.bank_statements
CREATE TABLE financial.bank_statements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    journal_id UUID NOT NULL REFERENCES financial.journals(id),
    name VARCHAR(100),
    reference VARCHAR(255),
    date DATE NOT NULL,
    date_done DATE,
    balance_start DECIMAL(20,6) DEFAULT 0,
    balance_end_real DECIMAL(20,6) DEFAULT 0,
    balance_end DECIMAL(20,6) GENERATED ALWAYS AS (balance_start + total_entry_encoding) STORED,
    total_entry_encoding DECIMAL(20,6) DEFAULT 0,
    status financial.statement_status DEFAULT 'draft',
    currency_id UUID REFERENCES core.currencies(id),
    is_complete BOOLEAN DEFAULT FALSE,
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: financial.bank_statement_lines
CREATE TABLE financial.bank_statement_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    statement_id UUID NOT NULL REFERENCES financial.bank_statements(id) ON DELETE CASCADE,
    sequence INTEGER DEFAULT 10,
    date DATE NOT NULL,
    payment_ref VARCHAR(255),
    ref VARCHAR(255),
    partner_id UUID REFERENCES core.partners(id),
    amount DECIMAL(20,6) NOT NULL,
    amount_currency DECIMAL(20,6),
    foreign_currency_id UUID REFERENCES core.currencies(id),
    transaction_type VARCHAR(50),
    narration TEXT,
    is_reconciled BOOLEAN DEFAULT FALSE,
    partner_bank_id UUID REFERENCES core.partner_banks(id),
    account_number VARCHAR(64),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

COMMENT ON TABLE financial.bank_statements IS 'Bank statements - Equivalent to account.bank.statement';
COMMENT ON TABLE financial.bank_statement_lines IS 'Bank statement lines - Equivalent to account.bank.statement.line';
```

**Dependencias:** COR-022 (partner_banks), financial.journals (existe)
**Impacto:** Conciliacion bancaria, flujo de caja

---

### 2.4 FINANCIAL - Tax Repartition

**ID:** COR-024
**Gap:** GAP-FIN-TBL-006
**Archivo:** `04-financial.sql`

```sql
-- ENUM para tipo de factura en reparticion
CREATE TYPE financial.repartition_type AS ENUM (
    'invoice',
    'refund'
);

-- Tabla: financial.tax_repartition_lines
CREATE TABLE financial.tax_repartition_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    tax_id UUID NOT NULL REFERENCES financial.taxes(id) ON DELETE CASCADE,
    repartition_type financial.repartition_type NOT NULL,
    sequence INTEGER DEFAULT 1,
    factor_percent DECIMAL(10,4) DEFAULT 100,
    account_id UUID REFERENCES financial.accounts(id),
    tag_ids UUID[],  -- account.account.tag references
    use_in_tax_closing BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_tax_repartition_tax ON financial.tax_repartition_lines(tax_id);
COMMENT ON TABLE financial.tax_repartition_lines IS 'Tax repartition lines - Equivalent to account.tax.repartition.line';
```

**Dependencias:** financial.taxes, financial.accounts (existen)
**Impacto:** Contabilidad fiscal correcta

---

### 2.5 INVENTORY - Stock Routes & Rules

**ID:** COR-025
**Gap:** GAP-INV-TBL-001, GAP-INV-TBL-002
**Archivo:** `05-inventory.sql`

```sql
-- ENUM para tipo de accion de regla
CREATE TYPE inventory.rule_action AS ENUM (
    'pull',
    'push',
    'pull_push',
    'buy',
    'manufacture'
);

-- ENUM para tipo de procurement
CREATE TYPE inventory.procurement_type AS ENUM (
    'make_to_stock',
    'make_to_order'
);

-- Tabla: inventory.routes
CREATE TABLE inventory.routes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    sequence INTEGER DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    product_selectable BOOLEAN DEFAULT TRUE,
    product_categ_selectable BOOLEAN DEFAULT TRUE,
    warehouse_selectable BOOLEAN DEFAULT TRUE,
    supplied_wh_id UUID REFERENCES inventory.warehouses(id),
    supplier_wh_id UUID REFERENCES inventory.warehouses(id),
    company_id UUID REFERENCES core.companies(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: inventory.stock_rules
CREATE TABLE inventory.stock_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    route_id UUID NOT NULL REFERENCES inventory.routes(id) ON DELETE CASCADE,
    sequence INTEGER DEFAULT 20,
    action inventory.rule_action NOT NULL,
    procure_method inventory.procurement_type DEFAULT 'make_to_stock',
    location_src_id UUID REFERENCES inventory.locations(id),
    location_dest_id UUID NOT NULL REFERENCES inventory.locations(id),
    picking_type_id UUID REFERENCES inventory.picking_types(id),
    delay INTEGER DEFAULT 0,  -- Lead time in days
    partner_address_id UUID REFERENCES core.partners(id),
    propagate_cancel BOOLEAN DEFAULT FALSE,
    propagate_carrier BOOLEAN DEFAULT TRUE,
    warehouse_id UUID REFERENCES inventory.warehouses(id),
    group_propagation_option VARCHAR(20) DEFAULT 'propagate',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla de relacion producto-rutas
CREATE TABLE inventory.product_routes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE CASCADE,
    route_id UUID NOT NULL REFERENCES inventory.routes(id) ON DELETE CASCADE,
    UNIQUE(product_id, route_id)
);

CREATE INDEX idx_routes_warehouse ON inventory.routes(supplied_wh_id);
CREATE INDEX idx_rules_route ON inventory.stock_rules(route_id);
CREATE INDEX idx_rules_locations ON inventory.stock_rules(location_src_id, location_dest_id);

COMMENT ON TABLE inventory.routes IS 'Stock routes - Equivalent to stock.route';
COMMENT ON TABLE inventory.stock_rules IS 'Stock rules - Equivalent to stock.rule';
```

**Dependencias:** inventory.warehouses, inventory.locations, inventory.picking_types (existen)
**Impacto:** Automatizacion de abastecimiento

---

### 2.6 HR - Asistencias

**ID:** COR-026
**Gap:** GAP-HR-TBL-001
**Archivo:** `12-hr.sql`

```sql
-- Tabla: hr.attendances
CREATE TABLE hr.attendances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    employee_id UUID NOT NULL REFERENCES hr.employees(id) ON DELETE CASCADE,
    check_in TIMESTAMP NOT NULL,
    check_out TIMESTAMP,
    worked_hours DECIMAL(10,4) GENERATED ALWAYS AS (
        CASE WHEN check_out IS NOT NULL
        THEN EXTRACT(EPOCH FROM (check_out - check_in)) / 3600.0
        ELSE NULL END
    ) STORED,
    overtime_hours DECIMAL(10,4) DEFAULT 0,
    is_overtime BOOLEAN DEFAULT FALSE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT valid_checkout CHECK (check_out IS NULL OR check_out > check_in)
);

CREATE INDEX idx_attendances_employee ON hr.attendances(employee_id);
CREATE INDEX idx_attendances_checkin ON hr.attendances(check_in);
CREATE INDEX idx_attendances_date ON hr.attendances(tenant_id, DATE(check_in));

COMMENT ON TABLE hr.attendances IS 'Employee attendances - Equivalent to hr.attendance';
```

**Dependencias:** hr.employees (existe)
**Impacto:** Control de horario, nominas

---

### 2.7 HR - Ausencias/Vacaciones

**ID:** COR-027
**Gap:** GAP-HR-TBL-002
**Archivo:** `12-hr.sql`

```sql
-- ENUM para tipo de ausencia
CREATE TYPE hr.leave_type_kind AS ENUM (
    'leave',
    'allocation'
);

-- ENUM para estado de ausencia
CREATE TYPE hr.leave_status AS ENUM (
    'draft',
    'confirm',
    'validate1',
    'validate',
    'refuse'
);

-- Tabla: hr.leave_types
CREATE TABLE hr.leave_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    code VARCHAR(20),
    leave_type hr.leave_type_kind DEFAULT 'leave',
    requires_allocation BOOLEAN DEFAULT TRUE,
    request_unit VARCHAR(20) DEFAULT 'day', -- day, half_day, hour
    allocation_type VARCHAR(20) DEFAULT 'no', -- no, fixed, fixed_allocation
    validity_start DATE,
    validity_stop DATE,
    max_leaves DECIMAL(10,2) DEFAULT 0,
    responsible_id UUID REFERENCES auth.users(id),
    color VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: hr.leaves
CREATE TABLE hr.leaves (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    employee_id UUID NOT NULL REFERENCES hr.employees(id),
    leave_type_id UUID NOT NULL REFERENCES hr.leave_types(id),
    name VARCHAR(255),
    request_date_from DATE NOT NULL,
    request_date_to DATE NOT NULL,
    date_from TIMESTAMP NOT NULL,
    date_to TIMESTAMP NOT NULL,
    number_of_days DECIMAL(10,2) NOT NULL,
    number_of_hours DECIMAL(10,2),
    status hr.leave_status DEFAULT 'draft',
    notes TEXT,
    manager_id UUID REFERENCES hr.employees(id),
    first_approver_id UUID REFERENCES auth.users(id),
    second_approver_id UUID REFERENCES auth.users(id),
    validated_by UUID REFERENCES auth.users(id),
    validated_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: hr.leave_allocations
CREATE TABLE hr.leave_allocations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    employee_id UUID NOT NULL REFERENCES hr.employees(id),
    leave_type_id UUID NOT NULL REFERENCES hr.leave_types(id),
    name VARCHAR(255),
    number_of_days DECIMAL(10,2) NOT NULL,
    date_from DATE,
    date_to DATE,
    status hr.leave_status DEFAULT 'draft',
    allocation_type VARCHAR(20) DEFAULT 'regular', -- regular, accrual
    notes TEXT,
    approved_by UUID REFERENCES auth.users(id),
    approved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_leaves_employee ON hr.leaves(employee_id);
CREATE INDEX idx_leaves_dates ON hr.leaves(date_from, date_to);
CREATE INDEX idx_leaves_status ON hr.leaves(status);

COMMENT ON TABLE hr.leave_types IS 'Leave types - Equivalent to hr.leave.type';
COMMENT ON TABLE hr.leaves IS 'Employee leaves - Equivalent to hr.leave';
COMMENT ON TABLE hr.leave_allocations IS 'Leave allocations - Equivalent to hr.leave.allocation';
```

**Dependencias:** hr.employees (existe)
**Impacto:** Gestion de ausencias

---

## 3. Correcciones P1 (Altas)

### 3.1 FINANCIAL - Fiscal Positions

**ID:** COR-028
**Gap:** GAP-FIN-TBL-003, GAP-FIN-TBL-004, GAP-FIN-TBL-005
**Archivo:** `04-financial.sql`

```sql
-- Tabla: financial.fiscal_positions
CREATE TABLE financial.fiscal_positions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    sequence INTEGER DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    company_id UUID REFERENCES core.companies(id),
    country_id UUID REFERENCES core.countries(id),
    country_group_id UUID,
    state_ids UUID[],
    zip_from VARCHAR(20),
    zip_to VARCHAR(20),
    auto_apply BOOLEAN DEFAULT FALSE,
    vat_required BOOLEAN DEFAULT FALSE,
    fiscal_country_codes TEXT,
    note TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: financial.fiscal_position_taxes
CREATE TABLE financial.fiscal_position_taxes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiscal_position_id UUID NOT NULL REFERENCES financial.fiscal_positions(id) ON DELETE CASCADE,
    tax_src_id UUID NOT NULL REFERENCES financial.taxes(id),
    tax_dest_id UUID REFERENCES financial.taxes(id)
);

-- Tabla: financial.fiscal_position_accounts
CREATE TABLE financial.fiscal_position_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiscal_position_id UUID NOT NULL REFERENCES financial.fiscal_positions(id) ON DELETE CASCADE,
    account_src_id UUID NOT NULL REFERENCES financial.accounts(id),
    account_dest_id UUID NOT NULL REFERENCES financial.accounts(id)
);

COMMENT ON TABLE financial.fiscal_positions IS 'Fiscal positions - Equivalent to account.fiscal.position';
```

---

### 3.2 PURCHASE - Funciones Faltantes

**ID:** COR-029
**Gap:** GAP-PUR-FUN-001, GAP-PUR-FUN-002
**Archivo:** `06-purchase.sql`

```sql
-- Funcion: purchase.button_cancel()
CREATE OR REPLACE FUNCTION purchase.button_cancel(p_order_id UUID)
RETURNS VOID AS $$
DECLARE
    v_order RECORD;
BEGIN
    SELECT * INTO v_order FROM purchase.purchase_orders WHERE id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Purchase order % not found', p_order_id;
    END IF;

    IF v_order.locked THEN
        RAISE EXCEPTION 'Cannot cancel locked order';
    END IF;

    IF v_order.status = 'cancelled' THEN
        RETURN;
    END IF;

    -- Cancel related pickings
    UPDATE inventory.pickings
    SET status = 'cancelled'
    WHERE origin_document_type = 'purchase_order'
    AND origin_document_id = p_order_id
    AND status != 'done';

    -- Update order status
    UPDATE purchase.purchase_orders
    SET status = 'cancelled', updated_at = NOW()
    WHERE id = p_order_id;
END;
$$ LANGUAGE plpgsql;

-- Funcion: purchase.button_draft()
CREATE OR REPLACE FUNCTION purchase.button_draft(p_order_id UUID)
RETURNS VOID AS $$
DECLARE
    v_order RECORD;
BEGIN
    SELECT * INTO v_order FROM purchase.purchase_orders WHERE id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Purchase order % not found', p_order_id;
    END IF;

    IF v_order.status NOT IN ('cancelled', 'sent') THEN
        RAISE EXCEPTION 'Can only set to draft from cancelled or sent state';
    END IF;

    UPDATE purchase.purchase_orders
    SET status = 'draft', updated_at = NOW()
    WHERE id = p_order_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION purchase.button_cancel IS 'Cancel purchase order - COR-029';
COMMENT ON FUNCTION purchase.button_draft IS 'Set purchase order to draft - COR-029';
```

---

### 3.3 CRM - Merge Leads

**ID:** COR-030
**Gap:** GAP-CRM-FUN-001
**Archivo:** `11-crm.sql`

```sql
-- Funcion: crm.merge_leads()
CREATE OR REPLACE FUNCTION crm.merge_leads(
    p_lead_ids UUID[],
    p_target_lead_id UUID
)
RETURNS UUID AS $$
DECLARE
    v_lead_id UUID;
    v_target RECORD;
BEGIN
    -- Validate target exists
    SELECT * INTO v_target FROM crm.leads WHERE id = p_target_lead_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Target lead % not found', p_target_lead_id;
    END IF;

    -- Merge activities, notes, and attachments to target
    FOREACH v_lead_id IN ARRAY p_lead_ids LOOP
        IF v_lead_id != p_target_lead_id THEN
            -- Move activities
            UPDATE crm.lead_activities
            SET lead_id = p_target_lead_id
            WHERE lead_id = v_lead_id;

            -- Accumulate expected revenue
            UPDATE crm.leads t
            SET expected_revenue = t.expected_revenue + COALESCE(
                (SELECT expected_revenue FROM crm.leads WHERE id = v_lead_id), 0
            )
            WHERE t.id = p_target_lead_id;

            -- Mark as merged (soft delete)
            UPDATE crm.leads
            SET is_deleted = TRUE,
                merged_into_id = p_target_lead_id,
                updated_at = NOW()
            WHERE id = v_lead_id;
        END IF;
    END LOOP;

    RETURN p_target_lead_id;
END;
$$ LANGUAGE plpgsql;

-- Add merged_into_id column
ALTER TABLE crm.leads ADD COLUMN IF NOT EXISTS merged_into_id UUID REFERENCES crm.leads(id);

COMMENT ON FUNCTION crm.merge_leads IS 'Merge multiple leads into one - COR-030';
```

---

### 3.4 INVENTORY - Scrap

**ID:** COR-031
**Gap:** GAP-INV-TBL-003
**Archivo:** `05-inventory.sql`

```sql
-- ENUM para estado de scrap
CREATE TYPE inventory.scrap_status AS ENUM (
    'draft',
    'done'
);

-- Tabla: inventory.stock_scrap
CREATE TABLE inventory.stock_scrap (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(100),
    product_id UUID NOT NULL REFERENCES inventory.products(id),
    product_uom_id UUID REFERENCES core.units_of_measure(id),
    lot_id UUID REFERENCES inventory.lots(id),
    scrap_qty DECIMAL(20,6) NOT NULL,
    scrap_location_id UUID NOT NULL REFERENCES inventory.locations(id),
    location_id UUID NOT NULL REFERENCES inventory.locations(id),
    move_id UUID REFERENCES inventory.stock_moves(id),
    picking_id UUID REFERENCES inventory.pickings(id),
    origin VARCHAR(255),
    date_done TIMESTAMP,
    status inventory.scrap_status DEFAULT 'draft',
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Funcion para validar scrap
CREATE OR REPLACE FUNCTION inventory.validate_scrap(p_scrap_id UUID)
RETURNS UUID AS $$
DECLARE
    v_scrap RECORD;
    v_move_id UUID;
BEGIN
    SELECT * INTO v_scrap FROM inventory.stock_scrap WHERE id = p_scrap_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Scrap record % not found', p_scrap_id;
    END IF;

    IF v_scrap.status = 'done' THEN
        RETURN v_scrap.move_id;
    END IF;

    -- Create stock move
    INSERT INTO inventory.stock_moves (
        tenant_id, product_id, product_uom_id, quantity,
        location_id, location_dest_id, origin, status
    ) VALUES (
        v_scrap.tenant_id, v_scrap.product_id, v_scrap.product_uom_id,
        v_scrap.scrap_qty, v_scrap.location_id, v_scrap.scrap_location_id,
        v_scrap.name, 'done'
    ) RETURNING id INTO v_move_id;

    -- Update scrap
    UPDATE inventory.stock_scrap
    SET status = 'done',
        move_id = v_move_id,
        date_done = NOW(),
        updated_at = NOW()
    WHERE id = p_scrap_id;

    RETURN v_move_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON TABLE inventory.stock_scrap IS 'Stock scrap - Equivalent to stock.scrap';
```

---

### 3.5 PROJECTS - Project Updates

**ID:** COR-032
**Gap:** GAP-PRJ-TBL-001
**Archivo:** `08-projects.sql`

```sql
-- ENUM para estado de update
CREATE TYPE projects.update_status AS ENUM (
    'on_track',
    'at_risk',
    'off_track',
    'done'
);

-- Tabla: projects.project_updates
CREATE TABLE projects.project_updates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    project_id UUID NOT NULL REFERENCES projects.projects(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    status projects.update_status DEFAULT 'on_track',
    progress INTEGER CHECK (progress >= 0 AND progress <= 100),
    date DATE NOT NULL DEFAULT CURRENT_DATE,
    description TEXT,
    user_id UUID NOT NULL REFERENCES auth.users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_project_updates_project ON projects.project_updates(project_id);
CREATE INDEX idx_project_updates_date ON projects.project_updates(date DESC);

COMMENT ON TABLE projects.project_updates IS 'Project updates - Equivalent to project.update';
```

---

## 4. Correcciones P2 (Medias)

### 4.1 SALES - Order Templates

**ID:** COR-033
**Gap:** Templates de venta
**Archivo:** `07-sales.sql`

```sql
-- Tabla: sales.order_templates
CREATE TABLE sales.order_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    note TEXT,
    number_of_days INTEGER DEFAULT 0,
    require_signature BOOLEAN DEFAULT FALSE,
    require_payment BOOLEAN DEFAULT FALSE,
    prepayment_percent DECIMAL(5,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabla: sales.order_template_lines
CREATE TABLE sales.order_template_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id UUID NOT NULL REFERENCES sales.order_templates(id) ON DELETE CASCADE,
    sequence INTEGER DEFAULT 10,
    product_id UUID REFERENCES inventory.products(id),
    name TEXT,
    quantity DECIMAL(20,6) DEFAULT 1,
    product_uom_id UUID REFERENCES core.units_of_measure(id),
    display_type VARCHAR(20) -- line_section, line_note
);

COMMENT ON TABLE sales.order_templates IS 'Sale order templates - Equivalent to sale.order.template';
```

---

### 4.2 CORE - Attachments

**ID:** COR-034
**Gap:** GAP-CORE-TBL-004
**Archivo:** `02-core.sql`

```sql
-- Tabla: core.attachments
CREATE TABLE core.attachments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES system.tenants(id),
    name VARCHAR(255) NOT NULL,
    res_model VARCHAR(255) NOT NULL,
    res_id UUID NOT NULL,
    description TEXT,
    type VARCHAR(20) DEFAULT 'binary', -- binary, url
    url VARCHAR(1024),
    store_fname VARCHAR(255),
    file_size INTEGER,
    checksum VARCHAR(64),
    mimetype VARCHAR(128),
    index_content TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    access_token VARCHAR(64),
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_attachments_res ON core.attachments(res_model, res_id);
CREATE INDEX idx_attachments_token ON core.attachments(access_token) WHERE access_token IS NOT NULL;

COMMENT ON TABLE core.attachments IS 'File attachments - Equivalent to ir.attachment';
```

---

## 5. Resumen de Correcciones

### 5.1 Por Archivo

| Archivo | Correcciones | Nuevas Tablas | Nuevas Funciones |
|---------|--------------|---------------|------------------|
| 02-core.sql | COR-021, COR-022, COR-034 | 4 | 0 |
| 04-financial.sql | COR-023, COR-024, COR-028 | 6 | 0 |
| 05-inventory.sql | COR-025, COR-031 | 4 | 1 |
| 06-purchase.sql | COR-029 | 0 | 2 |
| 07-sales.sql | COR-033 | 2 | 0 |
| 08-projects.sql | COR-032 | 1 | 0 |
| 11-crm.sql | COR-030 | 0 | 1 |
| 12-hr.sql | COR-026, COR-027 | 4 | 0 |
| **TOTAL** | **14** | **21** | **4** |

### 5.2 Orden de Ejecucion

1. **Grupo 1 (Sin dependencias):**
   - COR-021: core.states
   - COR-034: core.attachments
   - COR-026: hr.attendances
   - COR-032: projects.project_updates

2. **Grupo 2 (Depende de Grupo 1):**
   - COR-022: core.banks, core.partner_banks (depende de partners)
   - COR-027: hr.leaves (depende de employees)
   - COR-024: tax_repartition_lines (depende de taxes)

3. **Grupo 3 (Depende de Grupo 2):**
   - COR-023: bank_statements (depende de partner_banks)
   - COR-028: fiscal_positions (depende de taxes)
   - COR-025: routes, rules (depende de picking_types)

4. **Grupo 4 (Funciones):**
   - COR-029: purchase.button_cancel, button_draft
   - COR-030: crm.merge_leads
   - COR-031: inventory.validate_scrap

5. **Grupo 5 (Templates):**
   - COR-033: sales.order_templates

---

## 6. Dependencias entre Correcciones

```
COR-021 (states) ──────────────────────────────────────┐
                                                       ↓
COR-022 (banks) ──────→ COR-023 (bank_statements) ───→ Conciliacion

COR-024 (tax_repartition) ──→ COR-028 (fiscal_positions)

COR-025 (routes/rules) ──→ Automatizacion de inventario

COR-026 (attendances) ─┬─→ COR-027 (leaves) ──→ Nominas
                       │
                       └─→ Reportes HR

COR-030 (merge_leads) ──→ CRM mejorado
COR-031 (scrap) ──→ Gestion de mermas
COR-032 (updates) ──→ Seguimiento de proyectos
COR-033 (templates) ──→ Ventas agiles
COR-034 (attachments) ──→ Documentos en todo el sistema
```

---

## 7. Criterios de Aceptacion

### 7.1 Por Correccion

- [ ] SQL sintacticamente valido
- [ ] Indices creados para FKs
- [ ] COMMENTs agregados
- [ ] Constraints definidos
- [ ] Compatible con multi-tenancy (tenant_id)
- [ ] Sin romper dependencias existentes

### 7.2 Global

- [ ] Todos los archivos DDL ejecutables en orden
- [ ] No hay referencias circulares
- [ ] RLS compatible con nuevas tablas

---

## 8. Proximos Pasos

1. **FASE 4:** Validar dependencias detalladamente
2. **FASE 5:** Refinar orden de ejecucion
3. **FASE 6:** Aplicar correcciones
4. **FASE 7:** Validar implementacion

---

**Generado:** 2026-01-04
**Herramienta:** Claude Code
**Metodologia:** SCRUM/SIMCO