-- Law Firm ERP v2 - Phase 6: Matter Lifecycle Foundation
-- Migration: 003 - KYC, Conflicts, Matter Stages, Workflow

-- ====================
-- KYC Profiles
-- ====================
CREATE TABLE IF NOT EXISTS kyc_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  id_type ENUM('Ghana Card', 'Passport', 'Voter ID', 'SSNIT', 'Driver License', 'Other') NOT NULL,
  id_number VARCHAR(100) NOT NULL,
  id_expiry_date DATE NULL,
  id_document_path VARCHAR(500) NULL COMMENT 'Encrypted file path',
  source_of_funds VARCHAR(200) NULL,
  pep_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Politically Exposed Person',
  pep_notes TEXT NULL,
  aml_checklist JSON NULL COMMENT 'AML/KYC checklist items',
  risk_rating ENUM('Low', 'Medium', 'High') NULL,
  reviewed_by INT NULL,
  reviewed_at TIMESTAMP NULL,
  next_review_date DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_client (client_id),
  INDEX idx_id_number (id_number),
  INDEX idx_pep (pep_flag),
  INDEX idx_risk (risk_rating),
  CONSTRAINT fk_kyc_client FOREIGN KEY (client_id) 
    REFERENCES clients(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_kyc_reviewer FOREIGN KEY (reviewed_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Conflict Parties (for conflicts search)
-- ====================
CREATE TABLE IF NOT EXISTS conflict_parties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL COMMENT 'links to cases.id',
  party_type ENUM('Client', 'Related Party', 'Adverse Party', 'Counsel', 'Other') NOT NULL,
  name VARCHAR(200) NOT NULL,
  identifier VARCHAR(100) NULL COMMENT 'ID number, company reg, etc',
  relationship VARCHAR(200) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_matter (matter_id),
  INDEX idx_name (name),
  INDEX idx_identifier (identifier),
  FULLTEXT idx_name_search (name),
  CONSTRAINT fk_conflict_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Conflict Checks (audit trail)
-- ====================
CREATE TABLE IF NOT EXISTS conflict_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  check_query VARCHAR(500) NOT NULL COMMENT 'Search query used',
  run_by INT NOT NULL,
  run_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  result_count INT NOT NULL DEFAULT 0,
  result_summary TEXT NULL COMMENT 'Summary of findings',
  conflict_level ENUM('None', 'Name Similar', 'Indirect', 'Direct') NOT NULL DEFAULT 'None',
  override_flag TINYINT(1) NOT NULL DEFAULT 0,
  override_by INT NULL,
  override_at TIMESTAMP NULL,
  override_justification TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_matter (matter_id),
  INDEX idx_run_by (run_by),
  INDEX idx_conflict_level (conflict_level),
  INDEX idx_override (override_flag),
  CONSTRAINT fk_check_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_check_user FOREIGN KEY (run_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_check_override FOREIGN KEY (override_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Matter Stages (workflow tracking)
-- ====================
CREATE TABLE IF NOT EXISTS matter_stages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  stage VARCHAR(100) NOT NULL,
  stage_order INT NOT NULL DEFAULT 0,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ended_at TIMESTAMP NULL,
  sla_hours INT NULL COMMENT 'SLA in hours for this stage',
  sla_due_at TIMESTAMP NULL,
  is_overdue TINYINT(1) NOT NULL DEFAULT 0,
  transition_reason TEXT NULL,
  notes TEXT NULL,
  transitioned_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_matter (matter_id),
  INDEX idx_stage (stage),
  INDEX idx_started (started_at),
  INDEX idx_sla_due (sla_due_at),
  INDEX idx_overdue (is_overdue),
  CONSTRAINT fk_stage_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_stage_user FOREIGN KEY (transitioned_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Workflow Configuration (stage templates)
-- ====================
CREATE TABLE IF NOT EXISTS workflow_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  workflow_name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT NULL,
  stages JSON NOT NULL COMMENT 'Array of stage objects with name, order, sla_hours',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active),
  INDEX idx_default (is_default),
  CONSTRAINT fk_workflow_creator FOREIGN KEY (created_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Matter ID Configuration (for auto-generation)
-- ====================
CREATE TABLE IF NOT EXISTS matter_id_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pattern VARCHAR(100) NOT NULL COMMENT 'e.g., YEAR-DEPT-SEQ or DEPT/YEAR/SEQ',
  prefix VARCHAR(20) NULL,
  sep_char VARCHAR(5) NOT NULL DEFAULT '-' COMMENT 'Separator character',
  include_year TINYINT(1) NOT NULL DEFAULT 1,
  include_dept TINYINT(1) NOT NULL DEFAULT 1,
  sequence_start INT NOT NULL DEFAULT 1,
  sequence_padding INT NOT NULL DEFAULT 4 COMMENT 'Zero padding: 4 = 0001',
  current_sequence INT NOT NULL DEFAULT 0,
  reset_yearly TINYINT(1) NOT NULL DEFAULT 1,
  last_reset_year INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Insert Default Workflow
-- ====================
INSERT INTO workflow_config (workflow_name, description, stages, is_default, created_at) 
VALUES (
  'Standard Case Workflow',
  'Default workflow for most legal matters',
  '[
    {"name": "Intake", "order": 1, "sla_hours": 24},
    {"name": "Filed", "order": 2, "sla_hours": 168},
    {"name": "Discovery", "order": 3, "sla_hours": 720},
    {"name": "Pre-Trial", "order": 4, "sla_hours": 168},
    {"name": "Hearing", "order": 5, "sla_hours": 48},
    {"name": "Judgment", "order": 6, "sla_hours": 72},
    {"name": "Enforcement", "order": 7, "sla_hours": 720},
    {"name": "Closed", "order": 8, "sla_hours": null}
  ]',
  1,
  CURRENT_TIMESTAMP
) ON DUPLICATE KEY UPDATE id=id;

-- ====================
-- Insert Default Matter ID Pattern
-- ====================
INSERT INTO matter_id_config (
  pattern, sep_char, include_year, include_dept, 
  sequence_start, sequence_padding, reset_yearly, is_active
) 
VALUES (
  'YEAR-DEPT-SEQ', '-', 1, 1, 1, 4, 1, 1
);

-- ====================
-- Add workflow_id to cases table (optional) - Skip if already exists
-- ====================
-- Note: MySQL doesn't support IF NOT EXISTS for ALTER TABLE ADD COLUMN
-- So we use a procedure to check first

-- Add workflow_id if not exists
SET @dbname = DATABASE();
SET @tablename = 'cases';
SET @columnname = 'workflow_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  'ALTER TABLE cases ADD COLUMN workflow_id INT NULL AFTER case_status, ADD INDEX idx_workflow (workflow_id)'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add current_stage if not exists
SET @columnname = 'current_stage';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  'ALTER TABLE cases ADD COLUMN current_stage VARCHAR(100) NULL AFTER workflow_id, ADD INDEX idx_current_stage (current_stage)'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add matter_id if not exists
SET @columnname = 'matter_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  'ALTER TABLE cases ADD COLUMN matter_id VARCHAR(50) NULL AFTER case_number, ADD INDEX idx_matter_id (matter_id)'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ====================
-- Audit Log for Phase 6 tables
-- ====================
-- Audit triggers will be added in application layer for these tables:
-- - kyc_profiles (create, update, review)
-- - conflict_checks (create, override)
-- - matter_stages (create, transition)