-- ============================================================================
-- ASSETS SCHEMA - MMD-013 Asset Management
-- Mecánicas Diesel - ERP Suite
-- ============================================================================
-- Schema for asset/tool management with assignment tracking, audits, and maintenance
-- ============================================================================

-- Create schema
CREATE SCHEMA IF NOT EXISTS assets;

-- Set search path
SET search_path TO assets, public;

-- ============================================================================
-- ENUMS
-- ============================================================================

CREATE TYPE asset_status AS ENUM (
    'available',
    'assigned',
    'in_maintenance',
    'damaged',
    'retired'
);

CREATE TYPE asset_location AS ENUM (
    'warehouse',
    'unit',
    'technician',
    'external'
);

CREATE TYPE criticality_level AS ENUM (
    'low',
    'medium',
    'high',
    'critical'
);

CREATE TYPE assignee_type AS ENUM (
    'employee',
    'unit',
    'location'
);

CREATE TYPE assignment_status AS ENUM (
    'active',
    'returned',
    'overdue',
    'lost',
    'damaged'
);

CREATE TYPE audit_status AS ENUM (
    'in_progress',
    'completed',
    'cancelled'
);

CREATE TYPE audit_item_status AS ENUM (
    'found',
    'missing',
    'damaged',
    'extra'
);

CREATE TYPE item_condition AS ENUM (
    'good',
    'fair',
    'poor'
);

CREATE TYPE maintenance_type AS ENUM (
    'preventive',
    'corrective',
    'calibration'
);

CREATE TYPE maintenance_status AS ENUM (
    'scheduled',
    'in_progress',
    'completed',
    'cancelled'
);

-- ============================================================================
-- TABLE: asset_categories
-- ============================================================================

CREATE TABLE asset_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id UUID REFERENCES asset_categories(id) ON DELETE SET NULL,
    requires_service_types TEXT[], -- Service types that require this category
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT uq_asset_categories_name UNIQUE (tenant_id, name)
);

CREATE INDEX idx_asset_categories_tenant ON asset_categories(tenant_id);
CREATE INDEX idx_asset_categories_parent ON asset_categories(parent_id);

-- ============================================================================
-- TABLE: assets
-- ============================================================================

CREATE TABLE assets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    category_id UUID NOT NULL REFERENCES asset_categories(id),
    serial_number VARCHAR(100),
    qr_code VARCHAR(100),
    barcode VARCHAR(100),
    manufacturer VARCHAR(100),
    model VARCHAR(100),
    purchase_date DATE,
    purchase_cost DECIMAL(12, 2),
    warranty_expiry DATE,
    status asset_status DEFAULT 'available',
    current_location asset_location DEFAULT 'warehouse',
    current_assignee_id UUID,
    current_assignee_type assignee_type,
    criticality criticality_level DEFAULT 'medium',
    requires_calibration BOOLEAN DEFAULT FALSE,
    last_calibration_date DATE,
    next_calibration_date DATE,
    photo_url TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,

    CONSTRAINT uq_assets_code UNIQUE (tenant_id, code),
    CONSTRAINT uq_assets_qr_code UNIQUE (tenant_id, qr_code),
    CONSTRAINT uq_assets_barcode UNIQUE (tenant_id, barcode)
);

CREATE INDEX idx_assets_tenant ON assets(tenant_id);
CREATE INDEX idx_assets_category ON assets(category_id);
CREATE INDEX idx_assets_status ON assets(status);
CREATE INDEX idx_assets_location ON assets(current_location);
CREATE INDEX idx_assets_assignee ON assets(current_assignee_id);
CREATE INDEX idx_assets_qr ON assets(qr_code) WHERE qr_code IS NOT NULL;
CREATE INDEX idx_assets_barcode ON assets(barcode) WHERE barcode IS NOT NULL;
CREATE INDEX idx_assets_calibration ON assets(next_calibration_date) WHERE requires_calibration = TRUE;

-- ============================================================================
-- TABLE: asset_assignments
-- ============================================================================

CREATE TABLE asset_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    asset_id UUID NOT NULL REFERENCES assets(id),
    assignee_id UUID NOT NULL,
    assignee_type assignee_type NOT NULL,
    assigned_at TIMESTAMPTZ DEFAULT NOW(),
    assigned_by UUID NOT NULL,
    expected_return_at TIMESTAMPTZ,
    actual_return_at TIMESTAMPTZ,
    returned_to UUID,
    status assignment_status DEFAULT 'active',
    assignment_photo_url TEXT,
    return_photo_url TEXT,
    notes TEXT,
    signature_url TEXT,
    incident_id UUID, -- FK to rescue_order if applicable
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT chk_return_dates CHECK (actual_return_at IS NULL OR actual_return_at >= assigned_at)
);

CREATE INDEX idx_asset_assignments_tenant ON asset_assignments(tenant_id);
CREATE INDEX idx_asset_assignments_asset ON asset_assignments(asset_id);
CREATE INDEX idx_asset_assignments_assignee ON asset_assignments(assignee_id, assignee_type);
CREATE INDEX idx_asset_assignments_status ON asset_assignments(status);
CREATE INDEX idx_asset_assignments_incident ON asset_assignments(incident_id) WHERE incident_id IS NOT NULL;
CREATE INDEX idx_asset_assignments_active ON asset_assignments(asset_id) WHERE status = 'active';

-- ============================================================================
-- TABLE: asset_audits
-- ============================================================================

CREATE TABLE asset_audits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    audit_date DATE NOT NULL,
    auditor_id UUID NOT NULL,
    location_id UUID,
    unit_id UUID,
    technician_id UUID,
    status audit_status DEFAULT 'in_progress',
    total_assets INTEGER DEFAULT 0,
    found_assets INTEGER DEFAULT 0,
    missing_assets INTEGER DEFAULT 0,
    damaged_assets INTEGER DEFAULT 0,
    notes TEXT,
    started_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_asset_audits_tenant ON asset_audits(tenant_id);
CREATE INDEX idx_asset_audits_date ON asset_audits(audit_date);
CREATE INDEX idx_asset_audits_auditor ON asset_audits(auditor_id);
CREATE INDEX idx_asset_audits_status ON asset_audits(status);
CREATE INDEX idx_asset_audits_unit ON asset_audits(unit_id) WHERE unit_id IS NOT NULL;
CREATE INDEX idx_asset_audits_technician ON asset_audits(technician_id) WHERE technician_id IS NOT NULL;

-- ============================================================================
-- TABLE: asset_audit_items
-- ============================================================================

CREATE TABLE asset_audit_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    audit_id UUID NOT NULL REFERENCES asset_audits(id) ON DELETE CASCADE,
    asset_id UUID REFERENCES assets(id),
    status audit_item_status NOT NULL,
    condition item_condition,
    notes TEXT,
    photo_url TEXT,
    scanned_at TIMESTAMPTZ DEFAULT NOW(),
    scanned_by UUID,

    CONSTRAINT uq_audit_items_asset UNIQUE (audit_id, asset_id)
);

CREATE INDEX idx_asset_audit_items_audit ON asset_audit_items(audit_id);
CREATE INDEX idx_asset_audit_items_asset ON asset_audit_items(asset_id);
CREATE INDEX idx_asset_audit_items_status ON asset_audit_items(status);

-- ============================================================================
-- TABLE: asset_maintenance
-- ============================================================================

CREATE TABLE asset_maintenance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    asset_id UUID NOT NULL REFERENCES assets(id),
    maintenance_type maintenance_type NOT NULL,
    scheduled_date DATE,
    performed_date DATE,
    performed_by UUID,
    external_vendor VARCHAR(200),
    cost DECIMAL(12, 2),
    description TEXT NOT NULL,
    result TEXT,
    next_maintenance_date DATE,
    status maintenance_status DEFAULT 'scheduled',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID
);

CREATE INDEX idx_asset_maintenance_tenant ON asset_maintenance(tenant_id);
CREATE INDEX idx_asset_maintenance_asset ON asset_maintenance(asset_id);
CREATE INDEX idx_asset_maintenance_type ON asset_maintenance(maintenance_type);
CREATE INDEX idx_asset_maintenance_status ON asset_maintenance(status);
CREATE INDEX idx_asset_maintenance_scheduled ON asset_maintenance(scheduled_date) WHERE status = 'scheduled';

-- ============================================================================
-- FUNCTIONS
-- ============================================================================

-- Function to update asset status when assigned
CREATE OR REPLACE FUNCTION update_asset_on_assignment()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'active' THEN
        UPDATE assets
        SET
            status = 'assigned',
            current_location = CASE NEW.assignee_type
                WHEN 'employee' THEN 'technician'
                WHEN 'unit' THEN 'unit'
                ELSE 'external'
            END,
            current_assignee_id = NEW.assignee_id,
            current_assignee_type = NEW.assignee_type,
            updated_at = NOW()
        WHERE id = NEW.asset_id;
    ELSIF NEW.status IN ('returned', 'lost', 'damaged') AND OLD.status = 'active' THEN
        UPDATE assets
        SET
            status = CASE NEW.status
                WHEN 'lost' THEN 'retired'
                WHEN 'damaged' THEN 'damaged'
                ELSE 'available'
            END,
            current_location = 'warehouse',
            current_assignee_id = NULL,
            current_assignee_type = NULL,
            updated_at = NOW()
        WHERE id = NEW.asset_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_asset_assignment_status
    AFTER INSERT OR UPDATE OF status ON asset_assignments
    FOR EACH ROW
    EXECUTE FUNCTION update_asset_on_assignment();

-- Function to update audit counts
CREATE OR REPLACE FUNCTION update_audit_counts()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE asset_audits
    SET
        total_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id)),
        found_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'found'),
        missing_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'missing'),
        damaged_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'damaged')
    WHERE id = COALESCE(NEW.audit_id, OLD.audit_id);
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_items_count
    AFTER INSERT OR UPDATE OR DELETE ON asset_audit_items
    FOR EACH ROW
    EXECUTE FUNCTION update_audit_counts();

-- Function to auto-update timestamps
CREATE OR REPLACE FUNCTION assets_update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_assets_updated_at
    BEFORE UPDATE ON assets
    FOR EACH ROW
    EXECUTE FUNCTION assets_update_timestamp();

CREATE TRIGGER trg_asset_maintenance_updated_at
    BEFORE UPDATE ON asset_maintenance
    FOR EACH ROW
    EXECUTE FUNCTION assets_update_timestamp();

CREATE TRIGGER trg_asset_categories_updated_at
    BEFORE UPDATE ON asset_categories
    FOR EACH ROW
    EXECUTE FUNCTION assets_update_timestamp();

-- ============================================================================
-- VIEWS
-- ============================================================================

-- View: Assets with current assignment info
CREATE OR REPLACE VIEW vw_assets_with_assignment AS
SELECT
    a.*,
    c.name AS category_name,
    aa.id AS active_assignment_id,
    aa.assignee_id,
    aa.assignee_type,
    aa.assigned_at,
    aa.expected_return_at,
    CASE
        WHEN aa.expected_return_at IS NOT NULL AND aa.expected_return_at < NOW()
        THEN TRUE
        ELSE FALSE
    END AS is_overdue
FROM assets a
LEFT JOIN asset_categories c ON a.category_id = c.id
LEFT JOIN asset_assignments aa ON a.id = aa.asset_id AND aa.status = 'active';

-- View: Assets requiring calibration soon
CREATE OR REPLACE VIEW vw_assets_calibration_due AS
SELECT
    a.id,
    a.tenant_id,
    a.code,
    a.name,
    a.next_calibration_date,
    a.current_location,
    a.current_assignee_id,
    EXTRACT(DAY FROM a.next_calibration_date - NOW()) AS days_until_due
FROM assets a
WHERE a.requires_calibration = TRUE
  AND a.status != 'retired'
  AND a.next_calibration_date <= NOW() + INTERVAL '30 days'
ORDER BY a.next_calibration_date;

-- View: Asset assignment history
CREATE OR REPLACE VIEW vw_asset_assignment_history AS
SELECT
    aa.*,
    a.code AS asset_code,
    a.name AS asset_name,
    a.category_id,
    c.name AS category_name
FROM asset_assignments aa
JOIN assets a ON aa.asset_id = a.id
JOIN asset_categories c ON a.category_id = c.id
ORDER BY aa.assigned_at DESC;

-- View: Audit summary
CREATE OR REPLACE VIEW vw_audit_summary AS
SELECT
    au.id,
    au.tenant_id,
    au.audit_date,
    au.auditor_id,
    au.status,
    au.total_assets,
    au.found_assets,
    au.missing_assets,
    au.damaged_assets,
    CASE
        WHEN au.total_assets > 0
        THEN ROUND((au.found_assets::DECIMAL / au.total_assets) * 100, 2)
        ELSE 0
    END AS accuracy_percentage,
    au.started_at,
    au.completed_at,
    EXTRACT(EPOCH FROM (COALESCE(au.completed_at, NOW()) - au.started_at)) / 60 AS duration_minutes
FROM asset_audits au;

-- ============================================================================
-- ROW LEVEL SECURITY
-- ============================================================================

-- Enable RLS on all tables
ALTER TABLE asset_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE assets ENABLE ROW LEVEL SECURITY;
ALTER TABLE asset_assignments ENABLE ROW LEVEL SECURITY;
ALTER TABLE asset_audits ENABLE ROW LEVEL SECURITY;
ALTER TABLE asset_audit_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE asset_maintenance ENABLE ROW LEVEL SECURITY;

-- Create policies for tenant isolation
CREATE POLICY asset_categories_tenant_isolation ON asset_categories
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

CREATE POLICY assets_tenant_isolation ON assets
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

CREATE POLICY asset_assignments_tenant_isolation ON asset_assignments
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

CREATE POLICY asset_audits_tenant_isolation ON asset_audits
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

CREATE POLICY asset_audit_items_tenant_isolation ON asset_audit_items
    USING (audit_id IN (SELECT id FROM asset_audits WHERE tenant_id = current_setting('app.current_tenant')::UUID));

CREATE POLICY asset_maintenance_tenant_isolation ON asset_maintenance
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- ============================================================================
-- SEED DATA (sample categories)
-- ============================================================================

-- Note: In production, seed data should be added via migration scripts
-- These are example categories for reference:
/*
INSERT INTO asset_categories (tenant_id, name, description, sort_order) VALUES
    ('00000000-0000-0000-0000-000000000001', 'Herramientas Manuales', 'Llaves, desarmadores, pinzas', 1),
    ('00000000-0000-0000-0000-000000000001', 'Herramientas Eléctricas', 'Taladros, esmeriles, etc.', 2),
    ('00000000-0000-0000-0000-000000000001', 'Equipos de Diagnóstico', 'Scanners, multímetros', 3),
    ('00000000-0000-0000-0000-000000000001', 'Equipos de Rescate', 'Grúas, gatos hidráulicos', 4),
    ('00000000-0000-0000-0000-000000000001', 'Equipos de Seguridad', 'EPP, señalización', 5),
    ('00000000-0000-0000-0000-000000000001', 'Equipos de Medición', 'Calibradores, manómetros', 6);
*/

-- ============================================================================
-- GRANTS (adjust based on your database roles)
-- ============================================================================

-- Grant usage on schema
-- GRANT USAGE ON SCHEMA assets TO app_user;
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA assets TO app_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA assets TO app_user;

COMMENT ON SCHEMA assets IS 'MMD-013: Asset and tool management module';
COMMENT ON TABLE assets IS 'Master table for all trackable assets and tools';
COMMENT ON TABLE asset_categories IS 'Hierarchical categorization of assets';
COMMENT ON TABLE asset_assignments IS 'Track asset assignments to employees, units, or locations';
COMMENT ON TABLE asset_audits IS 'Physical inventory audits';
COMMENT ON TABLE asset_audit_items IS 'Individual items checked during an audit';
COMMENT ON TABLE asset_maintenance IS 'Maintenance records including calibration';