-- Document Management System Migration
-- Comprehensive schema for law firm and court document management

-- Document Versions Table
CREATE TABLE IF NOT EXISTS document_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  version_number INTEGER NOT NULL,
  version_label VARCHAR(50), -- e.g., "v1.0", "Draft", "Final"
  content TEXT,
  file_url TEXT,
  file_hash VARCHAR(64), -- SHA-256 hash for integrity
  file_size BIGINT,
  mime_type VARCHAR(100),
  change_summary TEXT,
  change_type VARCHAR(50), -- 'created', 'modified', 'restored', 'merged'
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(document_id, version_number)
);

CREATE INDEX idx_document_versions_document_id ON document_versions(document_id);
CREATE INDEX idx_document_versions_created_at ON document_versions(created_at);
CREATE INDEX idx_document_versions_created_by ON document_versions(created_by);

-- Document Templates Table
CREATE TABLE IF NOT EXISTS document_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100), -- 'contract', 'pleading', 'motion', 'brief', 'corporate', etc.
  subcategory VARCHAR(100),
  template_content TEXT NOT NULL, -- Template with variables {{variable_name}}
  variables JSONB, -- Schema/definition of variables
  metadata JSONB, -- Additional metadata (jurisdiction, practice area, etc.)
  version INTEGER NOT NULL DEFAULT 1,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_public BOOLEAN NOT NULL DEFAULT FALSE, -- Public library vs private
  tags TEXT[], -- For searchability
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(name, version)
);

CREATE INDEX idx_document_templates_category ON document_templates(category);
CREATE INDEX idx_document_templates_active ON document_templates(is_active);
CREATE INDEX idx_document_templates_tags ON document_templates USING GIN(tags);
CREATE INDEX idx_document_templates_public ON document_templates(is_public);

-- Legal Matters Table
CREATE TABLE IF NOT EXISTS legal_matters (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  matter_number VARCHAR(100) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  matter_type VARCHAR(100), -- 'litigation', 'transaction', 'advisory', 'regulatory', etc.
  status VARCHAR(50) NOT NULL DEFAULT 'open', -- 'open', 'closed', 'on_hold', 'archived'
  priority VARCHAR(20) DEFAULT 'normal', -- 'low', 'normal', 'high', 'urgent'
  client_id UUID REFERENCES users(id), -- Primary client
  responsible_attorney_id UUID REFERENCES users(id),
  practice_area VARCHAR(100),
  jurisdiction VARCHAR(100),
  court_name VARCHAR(255),
  case_number VARCHAR(100),
  opened_date DATE,
  closed_date DATE,
  billing_code VARCHAR(50),
  metadata JSONB, -- Additional matter-specific data
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_legal_matters_matter_number ON legal_matters(matter_number);
CREATE INDEX idx_legal_matters_status ON legal_matters(status);
CREATE INDEX idx_legal_matters_client_id ON legal_matters(client_id);
CREATE INDEX idx_legal_matters_responsible_attorney ON legal_matters(responsible_attorney_id);
CREATE INDEX idx_legal_matters_case_number ON legal_matters(case_number);

-- Matter Participants (attorneys, clients, parties, etc.)
CREATE TABLE IF NOT EXISTS matter_participants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  matter_id UUID NOT NULL REFERENCES legal_matters(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id),
  role VARCHAR(50) NOT NULL, -- 'attorney', 'client', 'opposing_counsel', 'witness', 'expert', etc.
  organization_name VARCHAR(255), -- For non-user participants
  email VARCHAR(255),
  phone VARCHAR(50),
  is_primary BOOLEAN DEFAULT FALSE,
  access_level VARCHAR(50) DEFAULT 'read', -- 'read', 'write', 'admin'
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(matter_id, user_id, role)
);

CREATE INDEX idx_matter_participants_matter_id ON matter_participants(matter_id);
CREATE INDEX idx_matter_participants_user_id ON matter_participants(user_id);

-- Matter-Document Relationships
CREATE TABLE IF NOT EXISTS matter_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  matter_id UUID NOT NULL REFERENCES legal_matters(id) ON DELETE CASCADE,
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  relationship_type VARCHAR(50) NOT NULL, -- 'pleading', 'exhibit', 'correspondence', 'discovery', 'motion', etc.
  folder_path VARCHAR(500), -- Virtual folder structure
  is_primary BOOLEAN DEFAULT FALSE, -- Primary document for matter
  display_order INTEGER,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(matter_id, document_id)
);

CREATE INDEX idx_matter_documents_matter_id ON matter_documents(matter_id);
CREATE INDEX idx_matter_documents_document_id ON matter_documents(document_id);
CREATE INDEX idx_matter_documents_relationship_type ON matter_documents(relationship_type);

-- Document Audit Log
CREATE TABLE IF NOT EXISTS document_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
  version_id UUID REFERENCES document_versions(id) ON DELETE SET NULL,
  matter_id UUID REFERENCES legal_matters(id) ON DELETE SET NULL,
  action VARCHAR(50) NOT NULL, -- 'created', 'viewed', 'downloaded', 'modified', 'deleted', 'shared', 'filed', etc.
  performed_by UUID REFERENCES users(id),
  performed_at TIMESTAMP NOT NULL DEFAULT NOW(),
  ip_address INET,
  user_agent TEXT,
  details JSONB, -- Additional action-specific details
  metadata JSONB
);

CREATE INDEX idx_document_audit_log_document_id ON document_audit_log(document_id);
CREATE INDEX idx_document_audit_log_performed_by ON document_audit_log(performed_by);
CREATE INDEX idx_document_audit_log_performed_at ON document_audit_log(performed_at);
CREATE INDEX idx_document_audit_log_action ON document_audit_log(action);
CREATE INDEX idx_document_audit_log_matter_id ON document_audit_log(matter_id);

-- Document Comments/Annotations
CREATE TABLE IF NOT EXISTS document_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  version_id UUID REFERENCES document_versions(id) ON DELETE SET NULL,
  parent_comment_id UUID REFERENCES document_comments(id) ON DELETE CASCADE, -- For threaded comments
  comment_text TEXT NOT NULL,
  comment_type VARCHAR(50) DEFAULT 'comment', -- 'comment', 'suggestion', 'question', 'resolution'
  status VARCHAR(50) DEFAULT 'open', -- 'open', 'resolved', 'dismissed'
  page_number INTEGER,
  x_position DECIMAL(10,2), -- For PDF annotations
  y_position DECIMAL(10,2),
  highlight_text TEXT, -- Selected text being commented on
  author_id UUID NOT NULL REFERENCES users(id),
  resolved_by UUID REFERENCES users(id),
  resolved_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_document_comments_document_id ON document_comments(document_id);
CREATE INDEX idx_document_comments_version_id ON document_comments(version_id);
CREATE INDEX idx_document_comments_author_id ON document_comments(author_id);
CREATE INDEX idx_document_comments_status ON document_comments(status);
CREATE INDEX idx_document_comments_parent ON document_comments(parent_comment_id);

-- Document Workflows
CREATE TABLE IF NOT EXISTS document_workflows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  workflow_type VARCHAR(50) NOT NULL, -- 'approval', 'review', 'signing', 'filing', 'publication'
  status VARCHAR(50) NOT NULL DEFAULT 'pending', -- 'pending', 'in_progress', 'completed', 'rejected', 'cancelled'
  priority VARCHAR(20) DEFAULT 'normal',
  initiated_by UUID NOT NULL REFERENCES users(id),
  initiated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMP,
  metadata JSONB, -- Workflow-specific configuration
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_document_workflows_document_id ON document_workflows(document_id);
CREATE INDEX idx_document_workflows_status ON document_workflows(status);
CREATE INDEX idx_document_workflows_type ON document_workflows(workflow_type);

-- Workflow Steps (approval, review, etc.)
CREATE TABLE IF NOT EXISTS workflow_steps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID NOT NULL REFERENCES document_workflows(id) ON DELETE CASCADE,
  step_number INTEGER NOT NULL,
  step_type VARCHAR(50) NOT NULL, -- 'approval', 'review', 'signature', 'notification'
  assigned_to UUID REFERENCES users(id),
  assigned_role VARCHAR(100), -- Role-based assignment
  status VARCHAR(50) NOT NULL DEFAULT 'pending', -- 'pending', 'in_progress', 'approved', 'rejected', 'skipped'
  due_date TIMESTAMP,
  completed_at TIMESTAMP,
  comments TEXT,
  metadata JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(workflow_id, step_number)
);

CREATE INDEX idx_workflow_steps_workflow_id ON workflow_steps(workflow_id);
CREATE INDEX idx_workflow_steps_assigned_to ON workflow_steps(assigned_to);
CREATE INDEX idx_workflow_steps_status ON workflow_steps(status);

-- Court Filings
CREATE TABLE IF NOT EXISTS court_filings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  matter_id UUID NOT NULL REFERENCES legal_matters(id) ON DELETE CASCADE,
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  filing_type VARCHAR(100) NOT NULL, -- 'pleading', 'motion', 'brief', 'exhibit', etc.
  court_name VARCHAR(255) NOT NULL,
  court_system VARCHAR(100), -- 'federal', 'state', 'municipal', etc.
  case_number VARCHAR(100),
  docket_number VARCHAR(100),
  filing_date DATE,
  filing_deadline DATE,
  status VARCHAR(50) NOT NULL DEFAULT 'draft', -- 'draft', 'submitted', 'accepted', 'rejected', 'filed'
  filing_reference VARCHAR(255), -- Court's reference number
  filing_confirmation TEXT,
  submitted_by UUID REFERENCES users(id),
  submitted_at TIMESTAMP,
  accepted_at TIMESTAMP,
  rejection_reason TEXT,
  metadata JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_court_filings_matter_id ON court_filings(matter_id);
CREATE INDEX idx_court_filings_document_id ON court_filings(document_id);
CREATE INDEX idx_court_filings_case_number ON court_filings(case_number);
CREATE INDEX idx_court_filings_status ON court_filings(status);
CREATE INDEX idx_court_filings_filing_date ON court_filings(filing_date);

-- Clause Library (for document assembly)
CREATE TABLE IF NOT EXISTS clause_library (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  title VARCHAR(255),
  clause_text TEXT NOT NULL,
  category VARCHAR(100), -- 'warranty', 'indemnification', 'termination', 'governing_law', etc.
  subcategory VARCHAR(100),
  jurisdiction VARCHAR(100),
  practice_area VARCHAR(100),
  variables JSONB, -- Variables in clause
  metadata JSONB,
  version INTEGER NOT NULL DEFAULT 1,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_public BOOLEAN NOT NULL DEFAULT FALSE,
  tags TEXT[],
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(name, version)
);

CREATE INDEX idx_clause_library_category ON clause_library(category);
CREATE INDEX idx_clause_library_tags ON clause_library USING GIN(tags);
CREATE INDEX idx_clause_library_active ON clause_library(is_active);

-- Document Checkout (for preventing concurrent edits)
CREATE TABLE IF NOT EXISTS document_checkouts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  checked_out_by UUID NOT NULL REFERENCES users(id),
  checked_out_at TIMESTAMP NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMP NOT NULL,
  lock_type VARCHAR(50) DEFAULT 'exclusive', -- 'exclusive', 'shared_read'
  notes TEXT,
  UNIQUE(document_id) -- Only one checkout per document
);

CREATE INDEX idx_document_checkouts_document_id ON document_checkouts(document_id);
CREATE INDEX idx_document_checkouts_user_id ON document_checkouts(checked_out_by);
CREATE INDEX idx_document_checkouts_expires_at ON document_checkouts(expires_at);

-- Document Retention Policies
CREATE TABLE IF NOT EXISTS document_retention_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  document_type VARCHAR(100), -- Applies to specific document types
  matter_type VARCHAR(100), -- Applies to specific matter types
  retention_period_years INTEGER NOT NULL,
  retention_trigger VARCHAR(50) DEFAULT 'creation', -- 'creation', 'matter_close', 'last_access'
  disposal_action VARCHAR(50) DEFAULT 'archive', -- 'archive', 'delete', 'review'
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_retention_policies_active ON document_retention_policies(is_active);
CREATE INDEX idx_retention_policies_document_type ON document_retention_policies(document_type);

-- Document Retention Records
CREATE TABLE IF NOT EXISTS document_retention_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  policy_id UUID NOT NULL REFERENCES document_retention_policies(id),
  retention_start_date DATE NOT NULL,
  retention_end_date DATE NOT NULL,
  status VARCHAR(50) DEFAULT 'active', -- 'active', 'expired', 'disposed', 'extended'
  disposed_at TIMESTAMP,
  disposed_by UUID REFERENCES users(id),
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_retention_records_document_id ON document_retention_records(document_id);
CREATE INDEX idx_retention_records_end_date ON document_retention_records(retention_end_date);
CREATE INDEX idx_retention_records_status ON document_retention_records(status);

-- Update documents table to add version tracking
ALTER TABLE documents ADD COLUMN IF NOT EXISTS current_version INTEGER DEFAULT 1;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS latest_version_id UUID REFERENCES document_versions(id);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS is_checked_out BOOLEAN DEFAULT FALSE;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS checked_out_by UUID REFERENCES users(id);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS checked_out_at TIMESTAMP;

-- Full-text search support (using PostgreSQL's full-text search)
ALTER TABLE documents ADD COLUMN IF NOT EXISTS search_vector tsvector;
CREATE INDEX IF NOT EXISTS idx_documents_search_vector ON documents USING GIN(search_vector);

-- Function to update search vector
CREATE OR REPLACE FUNCTION update_document_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_search_vector_update
  BEFORE INSERT OR UPDATE ON documents
  FOR EACH ROW
  EXECUTE FUNCTION update_document_search_vector();

-- Comments
COMMENT ON TABLE document_versions IS 'Version history for documents with full revision tracking';
COMMENT ON TABLE document_templates IS 'Legal document templates with variable substitution';
COMMENT ON TABLE legal_matters IS 'Legal matters/cases with full case management';
COMMENT ON TABLE matter_participants IS 'Participants in legal matters (attorneys, clients, parties)';
COMMENT ON TABLE matter_documents IS 'Relationship between matters and documents';
COMMENT ON TABLE document_audit_log IS 'Comprehensive audit trail for all document actions';
COMMENT ON TABLE document_comments IS 'Comments and annotations on documents';
COMMENT ON TABLE document_workflows IS 'Workflow management for document approval, review, signing';
COMMENT ON TABLE workflow_steps IS 'Individual steps in document workflows';
COMMENT ON TABLE court_filings IS 'Court filing records and e-filing tracking';
COMMENT ON TABLE clause_library IS 'Reusable clause library for document assembly';
COMMENT ON TABLE document_checkouts IS 'Document checkout/lock system to prevent concurrent edits';
COMMENT ON TABLE document_retention_policies IS 'Document retention and disposal policies';
COMMENT ON TABLE document_retention_records IS 'Retention tracking for individual documents';

