Facilities Management Database Tables

interface Asset {
  id: string;
  asset_tag: string;
  name: string;
  description: string;
  category: AssetCategory;
  manufacturer: string;
  model: string;
  serial_number: string;
  purchase_date: string;
  purchase_price: number;
  warranty_expiration?: string;
  location_id: string;
  assigned_to?: string;
  status: AssetStatus;
  condition: AssetCondition;
  // Depreciation
  depreciation_method?: string;
  useful_life_years?: number;
  salvage_value?: number;
  current_value?: number;
  // Maintenance
  last_maintenance_date?: string;
  next_maintenance_date?: string;
  maintenance_schedule_id?: string;
  // Custom
  custom_fields: Record<string, any>;
}

interface InventoryItem {
  id: string;
  item_number: string;
  name: string;
  description: string;
  category: string;
  unit_of_measure: string;
  reorder_point: number;
  reorder_quantity: number;
  min_quantity: number;
  max_quantity: number;
  current_quantity: number;  // Computed from locations
  unit_cost: number;
  vendor_id?: string;
}

interface WorkOrder {
  id: string;
  work_order_number: string;
  title: string;
  description: string;
  type: WorkOrderType;
  priority: Priority;
  status: WorkOrderStatus;
  // Location
  site_id: string;
  location: string;
  asset_id?: string;
  // Assignment
  requested_by: string;
  assigned_to?: string;
  team_id?: string;
  // Scheduling
  requested_date: string;
  scheduled_start?: string;
  scheduled_end?: string;
  actual_start?: string;
  actual_end?: string;
  // Financials
  estimated_hours?: number;
  actual_hours?: number;
  estimated_cost?: number;
  actual_cost?: number;
  // PM Link
  pm_schedule_id?: string;
  // Custom
  custom_fields: Record<string, any>;
}

open → assigned → in_progress → pending_parts → completed → closed
                        ↓
                    on_hold

interface PMTemplate {
  id: string;
  name: string;
  description: string;
  asset_category: string;
  frequency_type: 'time_based' | 'meter_based';
  frequency_value: number;
  frequency_unit: string;
  estimated_hours: number;
  checklist_items: ChecklistItem[];
  required_materials: Material[];
  skills_required: string[];
}

const { data } = await supabase
  .from('fm_assets')
  .select(`
    *,
    location:pf_sites(name),
    assigned_to:pf_profiles(first_name, last_name),
    maintenance_history:fm_asset_maintenance_history(
      maintenance_date,
      description,
      cost
    )
  `)
  .eq('organization_id', orgId)
  .eq('site_id', siteId)
  .eq('status', 'in_service');

const { data } = await supabase
  .from('fm_work_orders')
  .select(`
    *,
    asset:fm_assets(name, asset_tag),
    assigned_to:pf_profiles(first_name, last_name),
    materials:fm_work_order_materials(
      item:fm_inventory_items(name),
      quantity
    )
  `)
  .eq('organization_id', orgId)
  .in('status', ['open', 'assigned', 'in_progress'])
  .order('priority', { ascending: true });

const { data } = await supabase
  .from('fm_pm_schedules')
  .select(`
    *,
    template:fm_pm_templates(
      name,
      estimated_hours,
      checklist_items:fm_pm_template_checklist_items(*)
    ),
    asset:fm_assets(name, asset_tag, location_id)
  `)
  .eq('organization_id', orgId)
  .lte('next_due_date', addDays(new Date(), 7))
  .eq('is_active', true);

const { data } = await supabase
  .from('fm_inventory_item_locations')
  .select(`
    quantity,
    item:fm_inventory_items(
      item_number,
      name,
      reorder_point,
      reorder_quantity
    ),
    location:fm_inventory_locations(name)
  `)
  .eq('organization_id', orgId)
  .lt('quantity', supabase.raw('item.reorder_point'));

const { data } = await supabase
  .from('fm_vendor_work_orders')
  .select(`
    vendor:fm_vendors(name),
    work_order:fm_work_orders(
      status,
      actual_cost,
      actual_hours
    )
  `)
  .eq('organization_id', orgId);

fm_assets → fa_accounts (depreciation entries)
fm_work_orders → fa_vendor_bills (contractor invoices)
fm_inventory_transactions → fa_journal_entries (inventory adjustments)

fm_work_orders.assigned_to → hr_employees
fm_vendor_certifications → hr_credential_types (credential tracking)