-- ===========================================
-- MECANICAS DIESEL - Schema de Contabilidad Analitica
-- ===========================================
-- Resuelve: GAP-05
-- Permite P&L por orden de servicio
-- Referencia: SPEC-CONTABILIDAD-ANALITICA-MULTIDIMENSIONAL.md

-- ============================================
-- SCHEMA: analytics
-- ============================================
CREATE SCHEMA IF NOT EXISTS analytics;
COMMENT ON SCHEMA analytics IS 'Contabilidad analitica simplificada - costos e ingresos por orden';

-- Grants
GRANT USAGE ON SCHEMA analytics TO mecanicas_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA analytics TO mecanicas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT ALL ON TABLES TO mecanicas_user;

-- ============================================
-- CUENTAS ANALITICAS
-- ============================================

-- Tipos de cuenta analitica
CREATE TABLE analytics.account_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sequence INTEGER NOT NULL DEFAULT 10,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE analytics.account_types IS 'Clasificacion de cuentas analiticas';

-- Seed de tipos predeterminados para taller
INSERT INTO analytics.account_types (code, name, description, sequence) VALUES
('service_order', 'Orden de Servicio', 'Cuenta por orden de servicio individual', 1),
('project', 'Proyecto', 'Agrupacion de multiples ordenes', 2),
('vehicle', 'Vehiculo', 'Costos historicos por vehiculo', 3),
('customer', 'Cliente', 'Rentabilidad por cliente', 4),
('department', 'Departamento', 'Costos por area del taller', 5);

-- Cuentas analiticas
CREATE TABLE analytics.accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    code VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,

    -- Clasificacion
    account_type_id UUID NOT NULL REFERENCES analytics.account_types(id),

    -- Referencia al documento origen (opcional)
    res_model VARCHAR(100),                    -- ej: 'service_management.service_orders'
    res_id UUID,                               -- ID del documento

    -- Jerarquia (para agrupaciones)
    parent_id UUID REFERENCES analytics.accounts(id),

    -- Presupuesto (opcional)
    budget_amount DECIMAL(20,6) DEFAULT 0,

    -- Metadatos
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,

    CONSTRAINT uq_analytics_account UNIQUE(tenant_id, code)
);

COMMENT ON TABLE analytics.accounts IS 'Cuentas analiticas para tracking de costos/ingresos';
COMMENT ON COLUMN analytics.accounts.res_model IS 'Modelo relacionado (ej: service_orders)';
COMMENT ON COLUMN analytics.accounts.res_id IS 'ID del documento relacionado';

-- Indices para accounts
CREATE INDEX idx_analytics_accounts_tenant ON analytics.accounts(tenant_id);
CREATE INDEX idx_analytics_accounts_type ON analytics.accounts(account_type_id);
CREATE INDEX idx_analytics_accounts_parent ON analytics.accounts(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX idx_analytics_accounts_resource ON analytics.accounts(res_model, res_id) WHERE res_model IS NOT NULL;

-- RLS para accounts
SELECT create_tenant_rls_policies('analytics', 'accounts');

-- Trigger para updated_at
CREATE TRIGGER set_updated_at_analytics_accounts
    BEFORE UPDATE ON analytics.accounts
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_updated_at();

-- ============================================
-- LINEAS ANALITICAS (Movimientos)
-- ============================================

-- Categorias de linea (costo vs ingreso)
CREATE TYPE analytics.line_category AS ENUM ('cost', 'revenue', 'adjustment');

-- Lineas analiticas
CREATE TABLE analytics.lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Cuenta analitica
    account_id UUID NOT NULL REFERENCES analytics.accounts(id),

    -- Fecha y descripcion
    date DATE NOT NULL,
    name VARCHAR(256) NOT NULL,
    ref VARCHAR(100),                          -- Referencia externa (factura, orden, etc.)

    -- Importes
    amount DECIMAL(20,6) NOT NULL,             -- Positivo = ingreso, negativo = costo
    category analytics.line_category NOT NULL,
    unit_amount DECIMAL(20,6),                 -- Cantidad de unidades (horas, piezas)
    unit_cost DECIMAL(20,6),                   -- Costo unitario

    -- Origen del movimiento
    source_model VARCHAR(100),                 -- Modelo que genero la linea
    source_id UUID,                            -- ID del registro origen

    -- Detalle adicional
    product_id UUID,                           -- Producto/refaccion si aplica
    employee_id UUID,                          -- Empleado si es mano de obra

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID
);

COMMENT ON TABLE analytics.lines IS 'Movimientos de costos/ingresos en cuentas analiticas';
COMMENT ON COLUMN analytics.lines.amount IS 'Monto: positivo=ingreso, negativo=costo';
COMMENT ON COLUMN analytics.lines.unit_amount IS 'Cantidad (horas de mano de obra, unidades de refaccion)';

-- Indices para lines
CREATE INDEX idx_analytics_lines_tenant ON analytics.lines(tenant_id);
CREATE INDEX idx_analytics_lines_account ON analytics.lines(account_id);
CREATE INDEX idx_analytics_lines_date ON analytics.lines(date);
CREATE INDEX idx_analytics_lines_category ON analytics.lines(category);
CREATE INDEX idx_analytics_lines_source ON analytics.lines(source_model, source_id) WHERE source_model IS NOT NULL;
CREATE INDEX idx_analytics_lines_product ON analytics.lines(product_id) WHERE product_id IS NOT NULL;

-- RLS para lines
SELECT create_tenant_rls_policies('analytics', 'lines');

-- ============================================
-- FUNCIONES PARA GESTION ANALITICA
-- ============================================

-- Funcion para crear cuenta analitica automatica para orden de servicio
CREATE OR REPLACE FUNCTION analytics.create_service_order_account(
    p_tenant_id UUID,
    p_service_order_id UUID,
    p_order_number VARCHAR(50),
    p_customer_name VARCHAR(256) DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_account_id UUID;
    v_type_id UUID;
    v_code VARCHAR(30);
    v_name VARCHAR(150);
BEGIN
    -- Obtener tipo 'service_order'
    SELECT id INTO v_type_id
    FROM analytics.account_types
    WHERE code = 'service_order';

    -- Generar codigo y nombre
    v_code := 'OS-' || p_order_number;
    v_name := 'Orden ' || p_order_number;
    IF p_customer_name IS NOT NULL THEN
        v_name := v_name || ' - ' || LEFT(p_customer_name, 50);
    END IF;

    -- Insertar cuenta
    INSERT INTO analytics.accounts (
        tenant_id, code, name, account_type_id,
        res_model, res_id
    )
    VALUES (
        p_tenant_id, v_code, v_name, v_type_id,
        'service_management.service_orders', p_service_order_id
    )
    ON CONFLICT (tenant_id, code) DO UPDATE SET
        name = EXCLUDED.name,
        updated_at = NOW()
    RETURNING id INTO v_account_id;

    RETURN v_account_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION analytics.create_service_order_account IS 'Crea cuenta analitica automatica para orden de servicio';

-- Funcion para registrar costo de refaccion
CREATE OR REPLACE FUNCTION analytics.log_parts_cost(
    p_tenant_id UUID,
    p_account_id UUID,
    p_part_id UUID,
    p_part_name VARCHAR(256),
    p_quantity DECIMAL(20,6),
    p_unit_cost DECIMAL(20,6),
    p_source_model VARCHAR(100) DEFAULT NULL,
    p_source_id UUID DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_line_id UUID;
    v_total_cost DECIMAL(20,6);
BEGIN
    v_total_cost := p_quantity * p_unit_cost * -1;  -- Negativo porque es costo

    INSERT INTO analytics.lines (
        tenant_id, account_id, date, name, ref,
        amount, category, unit_amount, unit_cost,
        source_model, source_id, product_id, created_by
    )
    VALUES (
        p_tenant_id, p_account_id, CURRENT_DATE,
        'Refaccion: ' || p_part_name, NULL,
        v_total_cost, 'cost', p_quantity, p_unit_cost,
        p_source_model, p_source_id, p_part_id, get_current_user_id()
    )
    RETURNING id INTO v_line_id;

    RETURN v_line_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION analytics.log_parts_cost IS 'Registra costo de refacciones usadas';

-- Funcion para registrar costo de mano de obra
CREATE OR REPLACE FUNCTION analytics.log_labor_cost(
    p_tenant_id UUID,
    p_account_id UUID,
    p_employee_id UUID,
    p_employee_name VARCHAR(256),
    p_hours DECIMAL(20,6),
    p_hourly_rate DECIMAL(20,6),
    p_description VARCHAR(256) DEFAULT NULL,
    p_source_model VARCHAR(100) DEFAULT NULL,
    p_source_id UUID DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_line_id UUID;
    v_total_cost DECIMAL(20,6);
BEGIN
    v_total_cost := p_hours * p_hourly_rate * -1;  -- Negativo porque es costo

    INSERT INTO analytics.lines (
        tenant_id, account_id, date, name, ref,
        amount, category, unit_amount, unit_cost,
        source_model, source_id, employee_id, created_by
    )
    VALUES (
        p_tenant_id, p_account_id, CURRENT_DATE,
        COALESCE(p_description, 'Mano de obra: ' || p_employee_name), NULL,
        v_total_cost, 'cost', p_hours, p_hourly_rate,
        p_source_model, p_source_id, p_employee_id, get_current_user_id()
    )
    RETURNING id INTO v_line_id;

    RETURN v_line_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION analytics.log_labor_cost IS 'Registra costo de mano de obra';

-- Funcion para registrar ingreso (facturacion)
CREATE OR REPLACE FUNCTION analytics.log_revenue(
    p_tenant_id UUID,
    p_account_id UUID,
    p_amount DECIMAL(20,6),
    p_description VARCHAR(256),
    p_ref VARCHAR(100) DEFAULT NULL,
    p_source_model VARCHAR(100) DEFAULT NULL,
    p_source_id UUID DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_line_id UUID;
BEGIN
    INSERT INTO analytics.lines (
        tenant_id, account_id, date, name, ref,
        amount, category, unit_amount, unit_cost,
        source_model, source_id, created_by
    )
    VALUES (
        p_tenant_id, p_account_id, CURRENT_DATE,
        p_description, p_ref,
        ABS(p_amount), 'revenue', NULL, NULL,
        p_source_model, p_source_id, get_current_user_id()
    )
    RETURNING id INTO v_line_id;

    RETURN v_line_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION analytics.log_revenue IS 'Registra ingreso en cuenta analitica';

-- ============================================
-- VISTAS PARA REPORTES
-- ============================================

-- Vista de P&L por cuenta analitica
CREATE VIEW analytics.v_account_pnl AS
SELECT
    a.id as account_id,
    a.tenant_id,
    a.code,
    a.name,
    at.code as account_type,
    a.res_model,
    a.res_id,
    a.budget_amount,
    COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 0) as total_revenue,
    COALESCE(SUM(CASE WHEN l.category = 'cost' THEN ABS(l.amount) ELSE 0 END), 0) as total_cost,
    COALESCE(SUM(l.amount), 0) as net_profit,
    CASE
        WHEN COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 0) = 0 THEN 0
        ELSE ROUND(
            (COALESCE(SUM(l.amount), 0) /
             COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 1)) * 100,
            2
        )
    END as margin_percent,
    COUNT(DISTINCT l.id) as line_count
FROM analytics.accounts a
JOIN analytics.account_types at ON at.id = a.account_type_id
LEFT JOIN analytics.lines l ON l.account_id = a.id
WHERE a.is_active = true
GROUP BY a.id, a.tenant_id, a.code, a.name, at.code, a.res_model, a.res_id, a.budget_amount;

COMMENT ON VIEW analytics.v_account_pnl IS 'Estado de resultados por cuenta analitica';

-- Vista de detalle de costos por orden
CREATE VIEW analytics.v_service_order_costs AS
SELECT
    a.res_id as service_order_id,
    a.tenant_id,
    a.code as account_code,
    l.date,
    l.name,
    l.category,
    l.amount,
    l.unit_amount,
    l.unit_cost,
    l.product_id,
    l.employee_id,
    l.source_model,
    l.source_id,
    l.created_at
FROM analytics.accounts a
JOIN analytics.account_types at ON at.id = a.account_type_id AND at.code = 'service_order'
JOIN analytics.lines l ON l.account_id = a.id
WHERE a.res_model = 'service_management.service_orders'
ORDER BY l.created_at DESC;

COMMENT ON VIEW analytics.v_service_order_costs IS 'Detalle de costos e ingresos por orden de servicio';

-- Vista resumen mensual
CREATE VIEW analytics.v_monthly_summary AS
SELECT
    a.tenant_id,
    DATE_TRUNC('month', l.date) as month,
    at.code as account_type,
    COUNT(DISTINCT a.id) as account_count,
    SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END) as total_revenue,
    SUM(CASE WHEN l.category = 'cost' THEN ABS(l.amount) ELSE 0 END) as total_cost,
    SUM(l.amount) as net_profit
FROM analytics.lines l
JOIN analytics.accounts a ON a.id = l.account_id
JOIN analytics.account_types at ON at.id = a.account_type_id
GROUP BY a.tenant_id, DATE_TRUNC('month', l.date), at.code
ORDER BY month DESC, account_type;

COMMENT ON VIEW analytics.v_monthly_summary IS 'Resumen de rentabilidad mensual por tipo de cuenta';

-- ============================================
-- GRANTS ADICIONALES
-- ============================================
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA analytics TO mecanicas_user;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO mecanicas_user;