-- ===========================================
-- MECANICAS DIESEL - Schema vehicle_management
-- ===========================================
-- Vehículos diesel, flotas, motores

SET search_path TO vehicle_management, public;

-- -------------------------------------------
-- ENGINE_CATALOG - Catálogo de motores (global)
-- -------------------------------------------
CREATE TABLE vehicle_management.engine_catalog (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,

    cylinders INTEGER CHECK (cylinders > 0),
    displacement DECIMAL(5,2) CHECK (displacement > 0),

    fuel_type VARCHAR(20) DEFAULT 'diesel',

    horsepower_min INTEGER CHECK (horsepower_min > 0),
    horsepower_max INTEGER CHECK (horsepower_max > 0),
    torque_max INTEGER CHECK (torque_max > 0),

    injection_system VARCHAR(50),

    year_start INTEGER CHECK (year_start >= 1950),
    year_end INTEGER CHECK (year_end >= 1950),

    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_engine_model UNIQUE (make, model),
    CONSTRAINT chk_horsepower CHECK (horsepower_max >= horsepower_min),
    CONSTRAINT chk_years CHECK (year_end IS NULL OR year_end >= year_start)
);

-- Datos iniciales de motores comunes
INSERT INTO vehicle_management.engine_catalog (make, model, cylinders, displacement, horsepower_min, horsepower_max, torque_max, injection_system) VALUES
('Cummins', 'ISX15', 6, 14.9, 400, 600, 2050, 'common_rail'),
('Cummins', 'ISL9', 6, 8.9, 260, 380, 1250, 'common_rail'),
('Cummins', 'X15', 6, 14.9, 400, 605, 2050, 'common_rail'),
('Cummins', 'ISB6.7', 6, 6.7, 200, 325, 750, 'common_rail'),
('Detroit', 'DD15', 6, 14.8, 400, 505, 1850, 'common_rail'),
('Detroit', 'DD13', 6, 12.8, 350, 470, 1650, 'common_rail'),
('Paccar', 'MX-13', 6, 12.9, 380, 510, 1850, 'common_rail'),
('Paccar', 'MX-11', 6, 10.8, 355, 430, 1550, 'common_rail'),
('Navistar', 'MaxxForce 13', 6, 12.4, 410, 475, 1700, 'common_rail'),
('Volvo', 'D13', 6, 12.8, 375, 500, 1850, 'unit_injector'),
('Caterpillar', 'C15', 6, 15.2, 435, 625, 2050, 'unit_injector'),
('Caterpillar', 'C13', 6, 12.5, 380, 520, 1750, 'unit_injector');

-- -------------------------------------------
-- FLEETS - Flotas de vehículos
-- -------------------------------------------
CREATE TABLE vehicle_management.fleets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    name VARCHAR(200) NOT NULL,
    code VARCHAR(20),

    contact_name VARCHAR(200),
    contact_email VARCHAR(200),
    contact_phone VARCHAR(20),

    -- Condiciones comerciales
    discount_labor_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_labor_pct >= 0 AND discount_labor_pct <= 100),
    discount_parts_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_parts_pct >= 0 AND discount_parts_pct <= 100),
    credit_days INTEGER DEFAULT 0 CHECK (credit_days >= 0),
    credit_limit DECIMAL(12,2) DEFAULT 0 CHECK (credit_limit >= 0),

    vehicle_count INTEGER DEFAULT 0 CHECK (vehicle_count >= 0),

    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_fleets_tenant ON vehicle_management.fleets(tenant_id);
CREATE INDEX idx_fleets_name ON vehicle_management.fleets(name);

CREATE TRIGGER trg_fleets_updated_at
    BEFORE UPDATE ON vehicle_management.fleets
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

SELECT create_tenant_rls_policies('vehicle_management', 'fleets');

-- -------------------------------------------
-- VEHICLES - Vehículos registrados
-- -------------------------------------------
CREATE TABLE vehicle_management.vehicles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    customer_id UUID NOT NULL,  -- core.partners
    fleet_id UUID REFERENCES vehicle_management.fleets(id),

    vin VARCHAR(17),
    license_plate VARCHAR(15) NOT NULL,
    economic_number VARCHAR(20),

    make VARCHAR(50) NOT NULL,
    model VARCHAR(100) NOT NULL,
    year INTEGER NOT NULL CHECK (year >= 1950 AND year <= 2100),
    color VARCHAR(30),

    vehicle_type VARCHAR(30) DEFAULT 'truck'
        CHECK (vehicle_type IN ('truck', 'trailer', 'bus', 'pickup', 'other')),

    current_odometer INTEGER CHECK (current_odometer >= 0),
    odometer_updated_at TIMESTAMP WITH TIME ZONE,

    photo_url VARCHAR(500),

    status VARCHAR(20) DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'sold')),

    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_vehicle_plate UNIQUE (tenant_id, license_plate)
);

CREATE INDEX idx_vehicles_tenant ON vehicle_management.vehicles(tenant_id);
CREATE INDEX idx_vehicles_customer ON vehicle_management.vehicles(customer_id);
CREATE INDEX idx_vehicles_fleet ON vehicle_management.vehicles(fleet_id);
CREATE INDEX idx_vehicles_vin ON vehicle_management.vehicles(vin);
CREATE INDEX idx_vehicles_plate ON vehicle_management.vehicles(license_plate);

CREATE TRIGGER trg_vehicles_updated_at
    BEFORE UPDATE ON vehicle_management.vehicles
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

SELECT create_tenant_rls_policies('vehicle_management', 'vehicles');

-- -------------------------------------------
-- VEHICLE_ENGINES - Especificaciones del motor
-- -------------------------------------------
CREATE TABLE vehicle_management.vehicle_engines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    vehicle_id UUID NOT NULL REFERENCES vehicle_management.vehicles(id) ON DELETE CASCADE,

    engine_catalog_id UUID REFERENCES vehicle_management.engine_catalog(id),

    serial_number VARCHAR(50),

    horsepower INTEGER CHECK (horsepower > 0),
    torque INTEGER CHECK (torque > 0),

    ecm_model VARCHAR(50),
    ecm_software VARCHAR(50),

    injection_system VARCHAR(50),
    rail_pressure_max DECIMAL(10,2) CHECK (rail_pressure_max > 0),
    injector_count INTEGER CHECK (injector_count > 0),

    turbo_type VARCHAR(50) CHECK (turbo_type IN ('VGT', 'wastegate', 'twin', 'compound')),
    turbo_make VARCHAR(50),
    turbo_model VARCHAR(50),

    manufacture_date DATE,
    rebuild_date DATE,
    rebuild_odometer INTEGER CHECK (rebuild_odometer >= 0),

    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_vehicle_engines_vehicle ON vehicle_management.vehicle_engines(vehicle_id);
CREATE INDEX idx_vehicle_engines_serial ON vehicle_management.vehicle_engines(serial_number);
CREATE INDEX idx_vehicle_engines_catalog ON vehicle_management.vehicle_engines(engine_catalog_id);

CREATE TRIGGER trg_vehicle_engines_updated_at
    BEFORE UPDATE ON vehicle_management.vehicle_engines
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- -------------------------------------------
-- VEHICLE_HISTORY - Historial de cambios
-- -------------------------------------------
CREATE TABLE vehicle_management.vehicle_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    vehicle_id UUID NOT NULL REFERENCES vehicle_management.vehicles(id) ON DELETE CASCADE,

    field_name VARCHAR(50) NOT NULL,
    old_value TEXT,
    new_value TEXT,

    changed_by UUID,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_vehicle_history_vehicle ON vehicle_management.vehicle_history(vehicle_id);
CREATE INDEX idx_vehicle_history_date ON vehicle_management.vehicle_history(changed_at DESC);

-- -------------------------------------------
-- MAINTENANCE_REMINDERS - Recordatorios
-- -------------------------------------------
CREATE TABLE vehicle_management.maintenance_reminders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    vehicle_id UUID NOT NULL REFERENCES vehicle_management.vehicles(id) ON DELETE CASCADE,

    service_type VARCHAR(100) NOT NULL,
    service_id UUID,

    frequency_type VARCHAR(20) NOT NULL CHECK (frequency_type IN ('time', 'odometer', 'both')),

    interval_days INTEGER CHECK (interval_days > 0),
    interval_km INTEGER CHECK (interval_km > 0),

    last_service_date DATE,
    last_service_km INTEGER,

    next_due_date DATE,
    next_due_km INTEGER,

    notify_days_before INTEGER DEFAULT 7 CHECK (notify_days_before >= 0),
    notify_km_before INTEGER DEFAULT 1000 CHECK (notify_km_before >= 0),

    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed')),

    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_reminders_tenant ON vehicle_management.maintenance_reminders(tenant_id);
CREATE INDEX idx_reminders_vehicle ON vehicle_management.maintenance_reminders(vehicle_id);
CREATE INDEX idx_reminders_due_date ON vehicle_management.maintenance_reminders(next_due_date);

CREATE TRIGGER trg_reminders_updated_at
    BEFORE UPDATE ON vehicle_management.maintenance_reminders
    FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

SELECT create_tenant_rls_policies('vehicle_management', 'maintenance_reminders');

-- -------------------------------------------
-- REMINDER_NOTIFICATIONS - Notificaciones enviadas
-- -------------------------------------------
CREATE TABLE vehicle_management.reminder_notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reminder_id UUID NOT NULL REFERENCES vehicle_management.maintenance_reminders(id) ON DELETE CASCADE,

    notification_type VARCHAR(20) NOT NULL CHECK (notification_type IN ('email', 'sms', 'push', 'whatsapp')),

    sent_to VARCHAR(200),
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    status VARCHAR(20) DEFAULT 'sent' CHECK (status IN ('sent', 'delivered', 'failed')),

    error_message TEXT
);

CREATE INDEX idx_reminder_notif_reminder ON vehicle_management.reminder_notifications(reminder_id);

-- -------------------------------------------
-- VEHICLE_DOCUMENTS - Documentos del vehículo
-- -------------------------------------------
CREATE TABLE vehicle_management.vehicle_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    vehicle_id UUID NOT NULL REFERENCES vehicle_management.vehicles(id) ON DELETE CASCADE,

    document_type VARCHAR(50) NOT NULL
        CHECK (document_type IN ('registration', 'insurance', 'permit', 'verification', 'other')),

    document_number VARCHAR(100),

    issue_date DATE,
    expiry_date DATE,

    file_url VARCHAR(500),

    notes TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_vehicle_docs_vehicle ON vehicle_management.vehicle_documents(vehicle_id);
CREATE INDEX idx_vehicle_docs_expiry ON vehicle_management.vehicle_documents(expiry_date);

-- -------------------------------------------
-- VISTA: Vehicle Summary
-- -------------------------------------------
CREATE OR REPLACE VIEW vehicle_management.vw_vehicle_summary AS
SELECT
    v.id,
    v.tenant_id,
    v.license_plate,
    v.economic_number,
    v.make,
    v.model,
    v.year,
    v.current_odometer,
    v.status,
    v.customer_id,
    f.name as fleet_name,
    f.id as fleet_id,
    ec.make as engine_make,
    ec.model as engine_model,
    ve.serial_number as engine_serial,
    ve.horsepower,
    (SELECT COUNT(*) FROM service_management.service_orders so
     WHERE so.vehicle_id = v.id) as total_orders,
    (SELECT MAX(so.completed_at) FROM service_management.service_orders so
     WHERE so.vehicle_id = v.id AND so.status = 'completed') as last_service_date
FROM vehicle_management.vehicles v
LEFT JOIN vehicle_management.fleets f ON v.fleet_id = f.id
LEFT JOIN vehicle_management.vehicle_engines ve ON ve.vehicle_id = v.id
LEFT JOIN vehicle_management.engine_catalog ec ON ve.engine_catalog_id = ec.id
WHERE v.tenant_id = get_current_tenant_id();

COMMENT ON VIEW vehicle_management.vw_vehicle_summary IS 'Vista resumida de vehículos con filtro RLS';

-- -------------------------------------------
-- Trigger para actualizar vehicle_count en fleets
-- -------------------------------------------
CREATE OR REPLACE FUNCTION vehicle_management.update_fleet_vehicle_count()
RETURNS TRIGGER AS $$
BEGIN
    -- Actualizar conteo de la flota anterior (si existe)
    IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.fleet_id IS DISTINCT FROM NEW.fleet_id) THEN
        IF OLD.fleet_id IS NOT NULL THEN
            UPDATE vehicle_management.fleets
            SET vehicle_count = (
                SELECT COUNT(*) FROM vehicle_management.vehicles
                WHERE fleet_id = OLD.fleet_id AND status = 'active'
            )
            WHERE id = OLD.fleet_id;
        END IF;
    END IF;

    -- Actualizar conteo de la nueva flota
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND OLD.fleet_id IS DISTINCT FROM NEW.fleet_id) THEN
        IF NEW.fleet_id IS NOT NULL THEN
            UPDATE vehicle_management.fleets
            SET vehicle_count = (
                SELECT COUNT(*) FROM vehicle_management.vehicles
                WHERE fleet_id = NEW.fleet_id AND status = 'active'
            )
            WHERE id = NEW.fleet_id;
        END IF;
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_fleet_count
    AFTER INSERT OR UPDATE OR DELETE ON vehicle_management.vehicles
    FOR EACH ROW EXECUTE FUNCTION vehicle_management.update_fleet_vehicle_count();