-- =============================================================================
-- BeKind Backend — File Usage Tracking (Future)
-- Table: file_usages
-- Purpose: Track which records reference a file for orphan detection and safe deletion
-- Depends on: 002-file-schema.sql (files)
--
-- FUTURE: Uncomment and run this migration when implementing file usage tracking
-- =============================================================================

-- File usage tracking table
-- Uncomment below when implementing file usage tracking

-- CREATE TABLE IF NOT EXISTS file_usages (
--     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
--
--     file_id     UUID         NOT NULL REFERENCES files(id) ON DELETE CASCADE,
--     module_name VARCHAR(100) NOT NULL,    -- users | posts | products | messages
--     ref_id      UUID         NOT NULL,
--     field_name  VARCHAR(100),            -- avatar | cover_image | document
--
--     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
--
--     -- Prevent duplicate registrations for same field
--     CONSTRAINT uq_file_usage UNIQUE (file_id, module_name, ref_id, field_name)
-- );

-- =============================================================================
-- INDEXES
-- =============================================================================

-- CREATE INDEX IF NOT EXISTS idx_file_usages_file_id
--     ON file_usages(file_id);

-- Real-world query: WHERE file_id = ? AND module_name = ?
-- CREATE INDEX IF NOT EXISTS idx_file_usages_file_module
--     ON file_usages(file_id, module_name);

-- CREATE INDEX IF NOT EXISTS idx_file_usages_ref
--     ON file_usages(module_name, ref_id);
