-- Migration: Enhanced Document Metadata and Tags System
-- Date: 2025-10-04
-- Description: Updates document_metadata and document_tags tables to support hierarchical organization
-- and rich metadata for case management integration

-- ============================================================================
-- 1. Update document_metadata table
-- ============================================================================

-- Add new columns to existing document_metadata table
ALTER TABLE document_metadata 
  ADD COLUMN IF NOT EXISTS document_id uuid REFERENCES documents(document_id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS metadata_key varchar(100),
  ADD COLUMN IF NOT EXISTS metadata_value text,
  ADD COLUMN IF NOT EXISTS value_type varchar(20) DEFAULT 'text' 
    CHECK (value_type IN ('text','number','date','uuid','json')),
  ADD COLUMN IF NOT EXISTS searchable boolean DEFAULT true,
  ADD COLUMN IF NOT EXISTS indexed boolean DEFAULT false;

-- Migrate existing data to new columns
UPDATE document_metadata 
SET 
  metadata_key = meta_key,
  metadata_value = meta_value
WHERE metadata_key IS NULL;

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_doc_metadata_key ON document_metadata(metadata_key);
CREATE INDEX IF NOT EXISTS idx_doc_metadata_value ON document_metadata(metadata_value) WHERE searchable = true;
CREATE INDEX IF NOT EXISTS idx_doc_metadata_doc ON document_metadata(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_metadata_doc_key ON document_metadata(document_id, metadata_key);

-- Add unique constraint (document + key)
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conname = 'uq_document_metadata_doc_key'
  ) THEN
    ALTER TABLE document_metadata 
      ADD CONSTRAINT uq_document_metadata_doc_key 
      UNIQUE (document_id, metadata_key);
  END IF;
END $$;

-- ============================================================================
-- 2. Update document_tags table to support hierarchy
-- ============================================================================

-- Add new columns for hierarchical tags
ALTER TABLE document_tags
  ADD COLUMN IF NOT EXISTS parent_tag_id uuid REFERENCES document_tags(document_tag_id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS tag_type varchar(50) DEFAULT 'custom' 
    CHECK (tag_type IN ('system','category','custom','auto')),
  ADD COLUMN IF NOT EXISTS color varchar(7) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS icon varchar(50) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS sort_order integer DEFAULT 0;

-- Create indexes for hierarchy queries
CREATE INDEX IF NOT EXISTS idx_document_tags_parent ON document_tags(parent_tag_id);
CREATE INDEX IF NOT EXISTS idx_document_tags_doc ON document_tags(document_id);
CREATE INDEX IF NOT EXISTS idx_document_tags_tag ON document_tags(tag);

-- Add unique constraint (document + tag)
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint 
    WHERE conname = 'uq_document_tag'
  ) THEN
    ALTER TABLE document_tags 
      ADD CONSTRAINT uq_document_tag 
      UNIQUE (document_id, tag);
  END IF;
END $$;

-- ============================================================================
-- 3. Create helper view for case documents
-- ============================================================================

CREATE OR REPLACE VIEW case_documents AS
SELECT DISTINCT
  c.case_id,
  c.client_id,
  c.case_number,
  c.title as case_title,
  d.document_id,
  d.title as document_title,
  d.description,
  d.type,
  d.expiry_date,
  d.owner_id,
  d.updatedAt,
  COALESCE(
    json_agg(DISTINCT dt.tag) FILTER (WHERE dt.tag IS NOT NULL),
    '[]'::json
  ) as tags,
  COALESCE(
    json_object_agg(dm.metadata_key, dm.metadata_value) FILTER (WHERE dm.metadata_key IS NOT NULL),
    '{}'::json
  ) as metadata
FROM cases c
LEFT JOIN document_metadata dm_case ON 
  dm_case.metadata_key = 'case_id' 
  AND dm_case.metadata_value = c.case_id::text
LEFT JOIN documents d ON d.document_id = dm_case.document_id
LEFT JOIN document_tags dt ON dt.document_id = d.document_id
LEFT JOIN document_metadata dm ON dm.document_id = d.document_id
WHERE (d.isDeleted = FALSE OR d.isDeleted IS NULL) 
  AND (d.isArchived = FALSE OR d.isArchived IS NULL)
GROUP BY c.case_id, c.client_id, c.case_number, c.title, 
         d.document_id, d.title, d.description, d.type, d.expiry_date, d.owner_id, d.updatedAt;

-- ============================================================================
-- 4. Create generic helper function to link document to any entity
-- ============================================================================

CREATE OR REPLACE FUNCTION link_document_to_entity(
  p_document_id uuid,
  p_entity_type text,              -- e.g., 'case', 'client', 'project'
  p_entity_id uuid,
  p_entity_metadata jsonb DEFAULT '{}'::jsonb,
  p_auto_tag boolean DEFAULT true
) RETURNS void AS $$
DECLARE
  v_key text;
  v_value text;
BEGIN
  -- Add core linking metadata (entity_type_id = entity_id)
  EXECUTE format('
    INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type)
    VALUES ($1, $2, $3, ''uuid'')
    ON CONFLICT (document_id, metadata_key) 
    DO UPDATE SET metadata_value = EXCLUDED.metadata_value, updatedAt = now()'
  ) USING p_document_id, p_entity_type || '_id', p_entity_id::text;

  -- Add all metadata from jsonb object
  FOR v_key, v_value IN SELECT * FROM jsonb_each_text(p_entity_metadata)
  LOOP
    INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type)
    VALUES (p_document_id, v_key, v_value, 'text')
    ON CONFLICT (document_id, metadata_key) 
    DO UPDATE SET metadata_value = EXCLUDED.metadata_value, updatedAt = now();
  END LOOP;

  -- Add system tag for entity type
  IF p_auto_tag THEN
    INSERT INTO document_tags (document_id, tag, tag_type)
    VALUES (p_document_id, p_entity_type, 'system')
    ON CONFLICT (document_id, tag) DO NOTHING;
    
    -- Add specific entity tag if number/name available
    IF p_entity_metadata ? (p_entity_type || '_number') THEN
      INSERT INTO document_tags (document_id, tag, tag_type)
      VALUES (p_document_id, p_entity_type || ':' || (p_entity_metadata->>( p_entity_type || '_number')), 'system')
      ON CONFLICT (document_id, tag) DO NOTHING;
    ELSIF p_entity_metadata ? 'name' THEN
      INSERT INTO document_tags (document_id, tag, tag_type)
      VALUES (p_document_id, p_entity_type || ':' || (p_entity_metadata->>'name'), 'system')
      ON CONFLICT (document_id, tag) DO NOTHING;
    END IF;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Legacy function for backward compatibility
CREATE OR REPLACE FUNCTION link_document_to_case(
  p_document_id uuid,
  p_case_id uuid,
  p_auto_tag boolean DEFAULT true
) RETURNS void AS $$
DECLARE
  v_case_number text;
  v_case_title text;
  v_client_id uuid;
  v_client_name text;
  v_metadata jsonb;
BEGIN
  -- Get case details
  SELECT case_number, title, client_id 
  INTO v_case_number, v_case_title, v_client_id
  FROM cases WHERE case_id = p_case_id;

  -- Get client name
  SELECT name INTO v_client_name FROM clients WHERE client_id = v_client_id;

  -- Build metadata jsonb
  v_metadata := jsonb_build_object(
    'case_number', v_case_number,
    'case_title', v_case_title,
    'client_id', v_client_id::text,
    'client_name', v_client_name
  );

  -- Call generic function
  PERFORM link_document_to_entity(p_document_id, 'case', p_case_id, v_metadata, p_auto_tag);
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 5. Create generic function to get all documents for an entity
-- ============================================================================

CREATE OR REPLACE FUNCTION get_entity_documents(
  p_entity_type text,
  p_entity_id uuid
)
RETURNS TABLE (
  document_id uuid,
  title text,
  description text,
  type varchar(50),
  source_table text,
  source_context text,
  tags json,
  metadata json,
  updated_at timestamptz
) AS $$
DECLARE
  v_metadata_key text;
BEGIN
  v_metadata_key := p_entity_type || '_id';
  
  RETURN QUERY
  SELECT 
    d.document_id,
    d.title,
    d.description,
    d.type,
    dm_source.metadata_value as source_table,
    dm_context.metadata_value as source_context,
    COALESCE(json_agg(DISTINCT dt.tag) FILTER (WHERE dt.tag IS NOT NULL), '[]'::json) as tags,
    COALESCE(
      json_object_agg(dm.metadata_key, dm.metadata_value) 
      FILTER (WHERE dm.metadata_key IS NOT NULL AND dm.metadata_key NOT LIKE 'source_%'),
      '{}'::json
    ) as metadata,
    d.updatedAt
  FROM documents d
  INNER JOIN document_metadata dm_entity ON 
    dm_entity.document_id = d.document_id 
    AND dm_entity.metadata_key = v_metadata_key
    AND dm_entity.metadata_value = p_entity_id::text
  LEFT JOIN document_metadata dm_source ON 
    dm_source.document_id = d.document_id 
    AND dm_source.metadata_key = 'source_table'
  LEFT JOIN document_metadata dm_context ON 
    dm_context.document_id = d.document_id 
    AND dm_context.metadata_key = 'source_context'
  LEFT JOIN document_tags dt ON dt.document_id = d.document_id
  LEFT JOIN document_metadata dm ON dm.document_id = d.document_id
  WHERE d.isDeleted = FALSE AND d.isArchived = FALSE
  GROUP BY d.document_id, d.title, d.description, d.type, d.updatedAt,
           dm_source.metadata_value, dm_context.metadata_value;
END;
$$ LANGUAGE plpgsql;

-- Legacy function for backward compatibility
CREATE OR REPLACE FUNCTION get_case_documents(p_case_id uuid)
RETURNS TABLE (
  document_id uuid,
  title text,
  description text,
  type varchar(50),
  source_table text,
  source_context text,
  tags json,
  metadata json,
  updated_at timestamptz
) AS $$
BEGIN
  RETURN QUERY SELECT * FROM get_entity_documents('case', p_case_id);
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 6. Migrate existing data
-- ============================================================================

-- Migrate document_version_id references to document_id where possible
-- This joins through document_versions to get the parent document_id
UPDATE document_metadata dm
SET document_id = dv.document_id
FROM document_versions dv
WHERE dm.document_version_id = dv.document_version_id
  AND dm.document_id IS NULL;

-- Add system tags for existing case instruction documents
INSERT INTO document_tags (document_id, tag, tag_type)
SELECT DISTINCT
  unnest(string_to_array(document_ids, ','))::uuid as document_id,
  'case' as tag,
  'system' as tag_type
FROM case_instructions
WHERE document_ids IS NOT NULL 
  AND document_ids != ''
ON CONFLICT (document_id, tag) DO NOTHING;

-- Link case instruction documents with metadata
INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type)
SELECT 
  unnest(string_to_array(ci.document_ids, ','))::uuid as document_id,
  'case_id',
  ci.case_id::text,
  'uuid'
FROM case_instructions ci
WHERE ci.document_ids IS NOT NULL 
  AND ci.document_ids != ''
ON CONFLICT (document_id, metadata_key) DO UPDATE
SET metadata_value = EXCLUDED.metadata_value,
    updatedAt = now();

-- Add source_table metadata for case instruction documents
INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type)
SELECT 
  unnest(string_to_array(ci.document_ids, ','))::uuid as document_id,
  'source_table',
  'case_instructions',
  'text'
FROM case_instructions ci
WHERE ci.document_ids IS NOT NULL 
  AND ci.document_ids != ''
ON CONFLICT (document_id, metadata_key) DO UPDATE
SET metadata_value = EXCLUDED.metadata_value,
    updatedAt = now();

-- ============================================================================
-- 7. Create indexes for better performance
-- ============================================================================

-- Index for finding documents by case
CREATE INDEX IF NOT EXISTS idx_doc_metadata_case_lookup 
  ON document_metadata(metadata_value) 
  WHERE metadata_key = 'case_id';

-- Index for finding documents by client
CREATE INDEX IF NOT EXISTS idx_doc_metadata_client_lookup 
  ON document_metadata(metadata_value) 
  WHERE metadata_key = 'client_id';

-- Index for system tags
CREATE INDEX IF NOT EXISTS idx_doc_tags_system 
  ON document_tags(tag) 
  WHERE tag_type = 'system';

-- ============================================================================
-- 8. Add comments for documentation
-- ============================================================================

COMMENT ON TABLE document_metadata IS 'Enhanced metadata system for documents with support for case/client linking and searchable key-value pairs';
COMMENT ON COLUMN document_metadata.document_id IS 'Direct reference to document (replaces document_version_id for most use cases)';
COMMENT ON COLUMN document_metadata.metadata_key IS 'Standardized metadata key (e.g., case_id, client_id, source_table)';
COMMENT ON COLUMN document_metadata.metadata_value IS 'Metadata value stored as text, with value_type indicating how to interpret it';
COMMENT ON COLUMN document_metadata.value_type IS 'Data type hint: text, number, date, uuid, or json';
COMMENT ON COLUMN document_metadata.searchable IS 'Whether this metadata should be included in document searches';

COMMENT ON TABLE document_tags IS 'Hierarchical tag system for documents with support for system-generated and user-created tags';
COMMENT ON COLUMN document_tags.parent_tag_id IS 'Parent tag for hierarchical organization (e.g., case:MC001 under case)';
COMMENT ON COLUMN document_tags.tag_type IS 'Tag origin: system (auto-generated), category (folder replacement), custom (user-created), auto (from AI/rules)';
COMMENT ON COLUMN document_tags.color IS 'UI display color in hex format (e.g., #3B82F6)';
COMMENT ON COLUMN document_tags.icon IS 'Icon identifier for UI display (e.g., fa-folder, fa-tag)';

COMMENT ON VIEW case_documents IS 'Aggregated view of all documents linked to cases with tags and metadata';
COMMENT ON FUNCTION link_document_to_case IS 'Helper function to link a document to a case with automatic metadata and tagging';
COMMENT ON FUNCTION get_case_documents IS 'Returns all documents associated with a case, including source tracking and aggregated metadata';
