-- WireAdmin Phase 3A: Basic Billing (MVP)
-- Prefix: wa_bill_

-- Clientes de Facturación
CREATE TABLE IF NOT EXISTS wa_bill_clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    rut VARCHAR(12) NOT NULL,
    business_name VARCHAR(200) NOT NULL,
    fantasy_name VARCHAR(200),
    giro VARCHAR(200), -- Actividad económica
    address VARCHAR(255),
    comuna VARCHAR(100),
    city VARCHAR(100),
    email VARCHAR(150),
    phone VARCHAR(20),
    contact_name VARCHAR(150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_rut (company_id, rut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documentos de Facturación
CREATE TABLE IF NOT EXISTS wa_bill_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    client_id INT NOT NULL,
    document_type ENUM('factura', 'boleta', 'nota_credito', 'nota_debito') NOT NULL,
    folio INT NOT NULL, -- Número correlativo
    date DATE NOT NULL,
    due_date DATE,
    reference_doc_id INT NULL, -- Para NC/ND
    
    -- Totals
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0, -- IVA
    total DECIMAL(15,2) NOT NULL DEFAULT 0,
    
    -- Status
    status ENUM('draft', 'issued', 'paid', 'cancelled') DEFAULT 'draft',
    
    -- Metadata
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES wa_bill_clients(id),
    FOREIGN KEY (reference_doc_id) REFERENCES wa_bill_documents(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES wa_users(id),
    UNIQUE KEY unique_folio (company_id, document_type, folio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Líneas de Documento
CREATE TABLE IF NOT EXISTS wa_bill_document_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    line_number INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(15,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 19.00, -- IVA 19%
    line_total DECIMAL(15,2) NOT NULL,
    FOREIGN KEY (document_id) REFERENCES wa_bill_documents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Control de Folios (Simplificado para MVP)
CREATE TABLE IF NOT EXISTS wa_bill_folio_control (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    document_type ENUM('factura', 'boleta', 'nota_credito', 'nota_debito') NOT NULL,
    current_folio INT NOT NULL DEFAULT 0,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_folio_control (company_id, document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
