BEGIN; CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE SCHEMA IF NOT EXISTS assets; ALTER TABLE assets.asset_categories ADD COLUMN IF NOT EXISTS tenant_id uuid NULL, ADD COLUMN IF NOT EXISTS category_code character varying(50) NULL, ADD COLUMN IF NOT EXISTS category_name character varying(160) NULL, ADD COLUMN IF NOT EXISTS parent_category_id bigint NULL, ADD COLUMN IF NOT EXISTS description text NULL, ADD COLUMN IF NOT EXISTS default_depreciation_method character varying(60) NULL, ADD COLUMN IF NOT EXISTS default_useful_life_years integer NULL, ADD COLUMN IF NOT EXISTS residual_rate numeric(8, 4) NULL, ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT TRUE, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS created_by uuid NULL, ADD COLUMN IF NOT EXISTS updated_by uuid NULL, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone NULL, ADD COLUMN IF NOT EXISTS deleted_by uuid NULL; ALTER TABLE assets.assets ADD COLUMN IF NOT EXISTS tenant_id uuid NULL, ADD COLUMN IF NOT EXISTS asset_code character varying(60) NULL, ADD COLUMN IF NOT EXISTS asset_name character varying(180) NULL, ADD COLUMN IF NOT EXISTS asset_type character varying(80) NULL, ADD COLUMN IF NOT EXISTS category_id bigint NULL, ADD COLUMN IF NOT EXISTS location_id bigint NULL, ADD COLUMN IF NOT EXISTS department_id bigint NULL, ADD COLUMN IF NOT EXISTS cost_center_id bigint NULL, ADD COLUMN IF NOT EXISTS assigned_to_user_id uuid NULL, ADD COLUMN IF NOT EXISTS assigned_to_employee_id bigint NULL, ADD COLUMN IF NOT EXISTS assigned_to_label character varying(180) NULL, ADD COLUMN IF NOT EXISTS serial_number character varying(120) NULL, ADD COLUMN IF NOT EXISTS barcode character varying(120) NULL, ADD COLUMN IF NOT EXISTS qr_code character varying(120) NULL, ADD COLUMN IF NOT EXISTS manufacturer character varying(160) NULL, ADD COLUMN IF NOT EXISTS model character varying(160) NULL, ADD COLUMN IF NOT EXISTS vendor_name character varying(180) NULL, ADD COLUMN IF NOT EXISTS linked_po_number character varying(80) NULL, ADD COLUMN IF NOT EXISTS purchase_date date NULL, ADD COLUMN IF NOT EXISTS acquisition_date date NULL, ADD COLUMN IF NOT EXISTS capitalization_date date NULL, ADD COLUMN IF NOT EXISTS currency_code character varying(10) NOT NULL DEFAULT 'GHS', ADD COLUMN IF NOT EXISTS purchase_cost numeric(18, 2) NULL, ADD COLUMN IF NOT EXISTS current_value numeric(18, 2) NULL, ADD COLUMN IF NOT EXISTS residual_value numeric(18, 2) NULL, ADD COLUMN IF NOT EXISTS accumulated_depreciation numeric(18, 2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS useful_life_years integer NULL, ADD COLUMN IF NOT EXISTS depreciation_method character varying(60) NULL, ADD COLUMN IF NOT EXISTS asset_status character varying(50) NOT NULL DEFAULT 'active', ADD COLUMN IF NOT EXISTS condition_status character varying(50) NULL, ADD COLUMN IF NOT EXISTS maintenance_required boolean NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS maintenance_interval character varying(60) NULL, ADD COLUMN IF NOT EXISTS last_maintenance_date date NULL, ADD COLUMN IF NOT EXISTS next_maintenance_date date NULL, ADD COLUMN IF NOT EXISTS warranty_start_date date NULL, ADD COLUMN IF NOT EXISTS warranty_end_date date NULL, ADD COLUMN IF NOT EXISTS description text NULL, ADD COLUMN IF NOT EXISTS notes text NULL, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT TRUE, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS created_by uuid NULL, ADD COLUMN IF NOT EXISTS updated_by uuid NULL, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone NULL, ADD COLUMN IF NOT EXISTS deleted_by uuid NULL; ALTER TABLE assets.asset_maintenance ADD COLUMN IF NOT EXISTS tenant_id uuid NULL, ADD COLUMN IF NOT EXISTS maintenance_no character varying(60) NULL, ADD COLUMN IF NOT EXISTS asset_id bigint NULL, ADD COLUMN IF NOT EXISTS maintenance_type character varying(60) NULL, ADD COLUMN IF NOT EXISTS priority character varying(40) NULL, ADD COLUMN IF NOT EXISTS status character varying(50) NOT NULL DEFAULT 'open', ADD COLUMN IF NOT EXISTS scheduled_date date NULL, ADD COLUMN IF NOT EXISTS completed_date date NULL, ADD COLUMN IF NOT EXISTS assigned_to_user_id uuid NULL, ADD COLUMN IF NOT EXISTS vendor_name character varying(180) NULL, ADD COLUMN IF NOT EXISTS labor_cost numeric(18, 2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS materials_cost numeric(18, 2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS other_cost numeric(18, 2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS total_cost numeric(18, 2) GENERATED ALWAYS AS (labor_cost + materials_cost + other_cost) STORED, ADD COLUMN IF NOT EXISTS notes text NULL, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS created_by uuid NULL, ADD COLUMN IF NOT EXISTS updated_by uuid NULL, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone NULL, ADD COLUMN IF NOT EXISTS deleted_by uuid NULL; CREATE TABLE IF NOT EXISTS assets.asset_locations ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, location_code character varying(60) NOT NULL, location_name character varying(180) NOT NULL, location_type character varying(80) NULL, parent_location_id bigint NULL, org_location_id bigint NULL, address text NULL, city character varying(100) NULL, region character varying(100) NULL, country character varying(100) NULL DEFAULT 'Ghana', manager_user_id uuid NULL, capacity_count integer NULL, current_asset_count integer NOT NULL DEFAULT 0, notes text NULL, is_active boolean NOT NULL DEFAULT TRUE, is_deleted boolean NOT NULL DEFAULT FALSE, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by uuid NULL, updated_by uuid NULL, deleted_at timestamp without time zone NULL, deleted_by uuid NULL, CONSTRAINT fk_asset_locations_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_locations_parent FOREIGN KEY (parent_location_id) REFERENCES assets.asset_locations(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.asset_allocations ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, allocation_no character varying(60) NOT NULL, asset_id bigint NOT NULL, assigned_to_type character varying(40) NOT NULL DEFAULT 'employee', assigned_to_user_id uuid NULL, assigned_to_employee_id bigint NULL, assigned_to_label character varying(180) NULL, department_id bigint NULL, location_id bigint NULL, allocation_date date NOT NULL, expected_return_date date NULL, actual_return_date date NULL, condition_at_allocation character varying(50) NULL, condition_at_return character varying(50) NULL, status character varying(50) NOT NULL DEFAULT 'allocated', notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by uuid NULL, updated_by uuid NULL, CONSTRAINT fk_asset_allocations_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_allocations_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_transfers ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, transfer_no character varying(60) NOT NULL, asset_id bigint NOT NULL, from_location_id bigint NULL, to_location_id bigint NULL, from_department_id bigint NULL, to_department_id bigint NULL, from_assigned_to character varying(180) NULL, to_assigned_to character varying(180) NULL, requested_by uuid NULL, requested_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, transfer_date date NULL, reason text NULL, status character varying(50) NOT NULL DEFAULT 'pending', approved_by uuid NULL, approved_at timestamp without time zone NULL, completed_at timestamp without time zone NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_transfers_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_transfers_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_movement_logs ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, movement_no character varying(60) NULL, movement_type character varying(60) NOT NULL, from_location_id bigint NULL, to_location_id bigint NULL, from_label character varying(180) NULL, to_label character varying(180) NULL, moved_by uuid NULL, moved_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, reference_type character varying(80) NULL, reference_id bigint NULL, notes text NULL, CONSTRAINT fk_asset_movement_logs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_movement_logs_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_status_history ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, old_status character varying(50) NULL, new_status character varying(50) NOT NULL, old_condition character varying(50) NULL, new_condition character varying(50) NULL, reason text NULL, changed_by uuid NULL, changed_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_status_history_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_status_history_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_documents ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, document_no character varying(60) NULL, document_type character varying(80) NOT NULL, document_name character varying(180) NOT NULL, file_reference character varying(255) NULL, file_name character varying(255) NULL, file_size_bytes bigint NULL, mime_type character varying(120) NULL, issue_date date NULL, expiry_date date NULL, status character varying(50) NOT NULL DEFAULT 'active', notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by uuid NULL, CONSTRAINT fk_asset_documents_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_documents_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_audit_trail ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NULL, action character varying(80) NOT NULL, module_name character varying(120) NULL, field_name character varying(120) NULL, old_value text NULL, new_value text NULL, performed_by uuid NULL, performed_by_name character varying(180) NULL, role_name character varying(120) NULL, ip_address inet NULL, notes text NULL, metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_audit_trail_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_audit_trail_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.asset_capitalizations ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, capitalization_no character varying(60) NOT NULL, asset_id bigint NULL, expense_reference character varying(80) NULL, description text NOT NULL, expense_account character varying(120) NULL, amount numeric(18, 2) NOT NULL, currency_code character varying(10) NOT NULL DEFAULT 'GHS', category_id bigint NULL, department_id bigint NULL, requested_by uuid NULL, request_date date NOT NULL DEFAULT CURRENT_DATE, status character varying(50) NOT NULL DEFAULT 'pending', approved_by uuid NULL, approved_at timestamp without time zone NULL, capitalized_at timestamp without time zone NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_capitalizations_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_capitalizations_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.asset_depreciation_entries ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, period_year integer NOT NULL, period_month integer NULL, depreciation_method character varying(60) NOT NULL, opening_book_value numeric(18, 2) NOT NULL DEFAULT 0, depreciation_amount numeric(18, 2) NOT NULL DEFAULT 0, accumulated_depreciation numeric(18, 2) NOT NULL DEFAULT 0, closing_book_value numeric(18, 2) NOT NULL DEFAULT 0, posted_at timestamp without time zone NULL, posted_by uuid NULL, status character varying(50) NOT NULL DEFAULT 'draft', metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_depreciation_entries_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_depreciation_entries_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_revaluations ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, revaluation_no character varying(60) NOT NULL, asset_id bigint NOT NULL, revaluation_date date NOT NULL, previous_value numeric(18, 2) NOT NULL, revalued_amount numeric(18, 2) NOT NULL, valuation_method character varying(80) NULL, valuer_name character varying(180) NULL, reason text NULL, status character varying(50) NOT NULL DEFAULT 'pending', approved_by uuid NULL, approved_at timestamp without time zone NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_revaluations_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_revaluations_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_disposals ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, disposal_no character varying(60) NOT NULL, asset_id bigint NOT NULL, disposal_method character varying(80) NOT NULL, disposal_date date NULL, book_value numeric(18, 2) NOT NULL DEFAULT 0, disposal_value numeric(18, 2) NOT NULL DEFAULT 0, gain_loss_amount numeric(18, 2) NOT NULL DEFAULT 0, buyer_name character varying(180) NULL, reason text NULL, status character varying(50) NOT NULL DEFAULT 'draft', requested_by uuid NULL, approved_by uuid NULL, approved_at timestamp without time zone NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_disposals_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_disposals_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_decommissions ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, decommission_no character varying(60) NOT NULL, asset_id bigint NOT NULL, reason character varying(120) NOT NULL, requested_by uuid NULL, requested_date date NOT NULL DEFAULT CURRENT_DATE, approved_by uuid NULL, approved_at timestamp without time zone NULL, decommission_date date NULL, status character varying(50) NOT NULL DEFAULT 'draft', audit_steps jsonb NOT NULL DEFAULT '[]'::jsonb, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_decommissions_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_decommissions_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_procurement_orders ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, po_number character varying(80) NOT NULL, vendor_name character varying(180) NOT NULL, vendor_code character varying(80) NULL, order_date date NOT NULL, expected_delivery_date date NULL, currency_code character varying(10) NOT NULL DEFAULT 'GHS', subtotal_amount numeric(18, 2) NOT NULL DEFAULT 0, tax_amount numeric(18, 2) NOT NULL DEFAULT 0, total_amount numeric(18, 2) NOT NULL DEFAULT 0, status character varying(50) NOT NULL DEFAULT 'draft', converted_asset_id bigint NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by uuid NULL, updated_by uuid NULL, CONSTRAINT fk_asset_procurement_orders_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_procurement_orders_asset FOREIGN KEY (converted_asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.asset_procurement_order_items ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, procurement_order_id bigint NOT NULL, asset_id bigint NULL, item_description character varying(220) NOT NULL, category_id bigint NULL, quantity numeric(18, 4) NOT NULL DEFAULT 1, unit_cost numeric(18, 2) NOT NULL DEFAULT 0, tax_amount numeric(18, 2) NOT NULL DEFAULT 0, line_total numeric(18, 2) NOT NULL DEFAULT 0, status character varying(50) NOT NULL DEFAULT 'pending', created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_procurement_order_items_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_procurement_order_items_order FOREIGN KEY (procurement_order_id) REFERENCES assets.asset_procurement_orders(id) ON DELETE CASCADE, CONSTRAINT fk_asset_procurement_order_items_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.asset_vendor_assignments ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, vendor_name character varying(180) NOT NULL, vendor_code character varying(80) NULL, contract_type character varying(80) NOT NULL, contract_reference character varying(100) NULL, start_date date NOT NULL, end_date date NULL, status character varying(50) NOT NULL DEFAULT 'active', contact_name character varying(150) NULL, contact_email character varying(150) NULL, contact_phone character varying(50) NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_vendor_assignments_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_vendor_assignments_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_warranties ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, warranty_no character varying(80) NOT NULL, asset_id bigint NOT NULL, warranty_type character varying(80) NOT NULL, provider_name character varying(180) NOT NULL, start_date date NOT NULL, end_date date NOT NULL, coverage_value numeric(18, 2) NULL, currency_code character varying(10) NOT NULL DEFAULT 'GHS', claims_used integer NOT NULL DEFAULT 0, max_claims integer NULL, contact_info character varying(220) NULL, coverage_description text NULL, status character varying(50) NOT NULL DEFAULT 'active', created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_warranties_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_warranties_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_inspection_logs ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, inspection_no character varying(80) NOT NULL, asset_id bigint NOT NULL, inspection_type character varying(80) NOT NULL, inspection_date date NOT NULL, inspector_name character varying(180) NULL, inspector_user_id uuid NULL, result character varying(50) NOT NULL DEFAULT 'pending', condition_status character varying(50) NULL, findings text NULL, recommendations text NULL, next_inspection_date date NULL, checklist jsonb NOT NULL DEFAULT '[]'::jsonb, status character varying(50) NOT NULL DEFAULT 'open', created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_inspection_logs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_inspection_logs_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_compliance_certificates ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, certificate_no character varying(80) NOT NULL, asset_id bigint NOT NULL, certificate_type character varying(100) NOT NULL, issuing_authority character varying(180) NULL, issue_date date NOT NULL, expiry_date date NOT NULL, status character varying(50) NOT NULL DEFAULT 'active', file_reference character varying(255) NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_compliance_certificates_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_compliance_certificates_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.maintenance_schedules ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, schedule_no character varying(80) NOT NULL, asset_id bigint NOT NULL, maintenance_type character varying(80) NOT NULL, frequency character varying(80) NOT NULL, start_date date NOT NULL, next_due_date date NOT NULL, last_completed_date date NULL, assigned_to_user_id uuid NULL, vendor_name character varying(180) NULL, estimated_hours numeric(10, 2) NULL, estimated_cost numeric(18, 2) NULL, status character varying(50) NOT NULL DEFAULT 'active', notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_schedules_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_schedules_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.maintenance_requests ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, request_no character varying(80) NOT NULL, work_order_no character varying(80) NULL, asset_id bigint NOT NULL, schedule_id bigint NULL, location_id bigint NULL, request_type character varying(80) NOT NULL, priority character varying(40) NOT NULL DEFAULT 'medium', description text NOT NULL, reported_by character varying(180) NULL, reported_by_user_id uuid NULL, reported_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, assigned_to_user_id uuid NULL, assigned_to_name character varying(180) NULL, scheduled_date date NULL, estimated_hours numeric(10, 2) NULL, actual_hours numeric(10, 2) NULL, status character varying(50) NOT NULL DEFAULT 'open', technician_notes text NULL, completed_at timestamp without time zone NULL, cancelled_at timestamp without time zone NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_requests_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_requests_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_requests_schedule FOREIGN KEY (schedule_id) REFERENCES assets.maintenance_schedules(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.maintenance_plans ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, plan_no character varying(80) NOT NULL, plan_name character varying(180) NOT NULL, asset_id bigint NULL, category_id bigint NULL, planning_period_start date NOT NULL, planning_period_end date NOT NULL, planned_downtime_hours numeric(10, 2) NULL, planned_cost numeric(18, 2) NULL, status character varying(50) NOT NULL DEFAULT 'draft', notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_plans_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_plans_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.maintenance_materials ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, maintenance_request_id bigint NULL, asset_id bigint NULL, material_code character varying(80) NULL, material_name character varying(180) NOT NULL, quantity numeric(18, 4) NOT NULL DEFAULT 1, unit_cost numeric(18, 2) NOT NULL DEFAULT 0, total_cost numeric(18, 2) GENERATED ALWAYS AS (quantity * unit_cost) STORED, supplier_name character varying(180) NULL, status character varying(50) NOT NULL DEFAULT 'reserved', used_at timestamp without time zone NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_materials_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_materials_request FOREIGN KEY (maintenance_request_id) REFERENCES assets.maintenance_requests(id) ON DELETE SET NULL, CONSTRAINT fk_maintenance_materials_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS assets.maintenance_costs ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, maintenance_request_id bigint NULL, asset_id bigint NOT NULL, cost_date date NOT NULL DEFAULT CURRENT_DATE, cost_type character varying(80) NOT NULL, description text NULL, currency_code character varying(10) NOT NULL DEFAULT 'GHS', amount numeric(18, 2) NOT NULL DEFAULT 0, vendor_name character varying(180) NULL, invoice_reference character varying(100) NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_costs_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_costs_request FOREIGN KEY (maintenance_request_id) REFERENCES assets.maintenance_requests(id) ON DELETE SET NULL, CONSTRAINT fk_maintenance_costs_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.maintenance_history ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, maintenance_request_id bigint NULL, asset_id bigint NOT NULL, work_order_no character varying(80) NULL, maintenance_type character varying(80) NOT NULL, completed_date date NOT NULL, technician_name character varying(180) NULL, downtime_hours numeric(10, 2) NULL, total_cost numeric(18, 2) NOT NULL DEFAULT 0, outcome character varying(80) NULL, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_history_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_history_request FOREIGN KEY (maintenance_request_id) REFERENCES assets.maintenance_requests(id) ON DELETE SET NULL, CONSTRAINT fk_maintenance_history_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.spare_parts ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, part_number character varying(80) NOT NULL, part_name character varying(180) NOT NULL, category character varying(100) NULL, unit_cost numeric(18, 2) NOT NULL DEFAULT 0, currency_code character varying(10) NOT NULL DEFAULT 'GHS', current_stock numeric(18, 4) NOT NULL DEFAULT 0, min_stock numeric(18, 4) NOT NULL DEFAULT 0, max_stock numeric(18, 4) NULL, supplier_name character varying(180) NULL, lead_time_days integer NULL, last_used_date date NULL, status character varying(50) NOT NULL DEFAULT 'in_stock', created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_spare_parts_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS assets.asset_spare_parts_bom ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, asset_id bigint NOT NULL, spare_part_id bigint NOT NULL, quantity_required numeric(18, 4) NOT NULL DEFAULT 1, replacement_interval character varying(80) NULL, is_critical boolean NOT NULL DEFAULT FALSE, notes text NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_asset_spare_parts_bom_tenant FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE, CONSTRAINT fk_asset_spare_parts_bom_asset FOREIGN KEY (asset_id) REFERENCES assets.assets(id) ON DELETE CASCADE, CONSTRAINT fk_asset_spare_parts_bom_part FOREIGN KEY (spare_part_id) REFERENCES assets.spare_parts(id) ON DELETE CASCADE ); CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_categories_tenant_code ON assets.asset_categories (tenant_id, category_code) WHERE tenant_id IS NOT NULL AND category_code IS NOT NULL AND COALESCE(is_deleted, FALSE) = FALSE; CREATE UNIQUE INDEX IF NOT EXISTS uq_assets_tenant_code ON assets.assets (tenant_id, asset_code) WHERE tenant_id IS NOT NULL AND asset_code IS NOT NULL AND COALESCE(is_deleted, FALSE) = FALSE; CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_locations_tenant_code ON assets.asset_locations (tenant_id, location_code) WHERE is_deleted = FALSE; CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_allocations_tenant_no ON assets.asset_allocations (tenant_id, allocation_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_transfers_tenant_no ON assets.asset_transfers (tenant_id, transfer_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_procurement_orders_tenant_po ON assets.asset_procurement_orders (tenant_id, po_number); CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_warranties_tenant_no ON assets.asset_warranties (tenant_id, warranty_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_spare_parts_tenant_number ON assets.spare_parts (tenant_id, part_number); CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_spare_parts_bom_asset_part ON assets.asset_spare_parts_bom (tenant_id, asset_id, spare_part_id); CREATE INDEX IF NOT EXISTS idx_assets_register_tenant_status ON assets.assets (tenant_id, asset_status) WHERE COALESCE(is_deleted, FALSE) = FALSE; CREATE INDEX IF NOT EXISTS idx_assets_register_tenant_category ON assets.assets (tenant_id, category_id) WHERE COALESCE(is_deleted, FALSE) = FALSE; CREATE INDEX IF NOT EXISTS idx_assets_register_tenant_location ON assets.assets (tenant_id, location_id) WHERE COALESCE(is_deleted, FALSE) = FALSE; CREATE INDEX IF NOT EXISTS idx_asset_allocations_asset ON assets.asset_allocations (asset_id, status); CREATE INDEX IF NOT EXISTS idx_asset_transfers_asset ON assets.asset_transfers (asset_id, status); CREATE INDEX IF NOT EXISTS idx_asset_movement_logs_asset ON assets.asset_movement_logs (asset_id, moved_at DESC); CREATE INDEX IF NOT EXISTS idx_asset_audit_trail_asset ON assets.asset_audit_trail (asset_id, created_at DESC); CREATE INDEX IF NOT EXISTS idx_asset_depreciation_entries_period ON assets.asset_depreciation_entries (tenant_id, period_year, period_month); CREATE INDEX IF NOT EXISTS idx_maintenance_requests_tenant_status ON assets.maintenance_requests (tenant_id, status, priority); CREATE INDEX IF NOT EXISTS idx_maintenance_schedules_due ON assets.maintenance_schedules (tenant_id, next_due_date, status); CREATE INDEX IF NOT EXISTS idx_maintenance_costs_asset_date ON assets.maintenance_costs (asset_id, cost_date DESC); CREATE INDEX IF NOT EXISTS idx_asset_warranties_expiry ON assets.asset_warranties (tenant_id, end_date, status); CREATE INDEX IF NOT EXISTS idx_asset_certificates_expiry ON assets.asset_compliance_certificates (tenant_id, expiry_date, status); COMMIT;