-- Law Firm ERP v2 - Phase 7: Court Calendaring & Evidence Management
-- Migration: 004 - Ghana Courts, Court Calendars, Evidence Chain-of-Custody

-- ====================
-- Ghana Court Taxonomy
-- ====================
CREATE TABLE IF NOT EXISTS court_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  court_level ENUM('District Court', 'Circuit Court', 'High Court', 'Court of Appeal', 'Supreme Court') NOT NULL,
  location VARCHAR(200) NOT NULL COMMENT 'City/Region in Ghana',
  court_name VARCHAR(300) NOT NULL,
  address TEXT NULL,
  contact_phone VARCHAR(50) NULL,
  contact_email VARCHAR(100) 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,
  UNIQUE KEY unique_court (court_level, location, court_name),
  INDEX idx_location (location),
  INDEX idx_level (court_level),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Court Calendars (extends hearings table)
-- ====================
CREATE TABLE IF NOT EXISTS court_calendars (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL COMMENT 'Links to cases.id',
  hearing_id INT NULL COMMENT 'Links to existing hearings table if applicable',
  court_type_id INT NULL COMMENT 'Links to court_types table',
  court_level VARCHAR(50) NOT NULL,
  court_location VARCHAR(200) NOT NULL,
  court_room VARCHAR(50) NULL,
  judge_name VARCHAR(200) NULL,
  docket_number VARCHAR(100) NULL,
  hearing_date DATE NOT NULL,
  hearing_time TIME NULL,
  hearing_type ENUM('Hearing', 'Motion', 'Trial', 'Ruling', 'Judgment', 'Mention', 'Case Management', 'Other') DEFAULT 'Hearing',
  duration_minutes INT NULL DEFAULT 60,
  status ENUM('Scheduled', 'Confirmed', 'Postponed', 'Completed', 'Cancelled') DEFAULT 'Scheduled',
  assigned_lawyer_id INT NOT NULL,
  notes TEXT NULL,
  postponement_reason TEXT NULL,
  outcome TEXT NULL,
  created_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_hearing (hearing_id),
  INDEX idx_court_type (court_type_id),
  INDEX idx_date (hearing_date),
  INDEX idx_lawyer (assigned_lawyer_id),
  INDEX idx_status (status),
  INDEX idx_datetime (hearing_date, hearing_time),
  CONSTRAINT fk_calendar_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_calendar_hearing FOREIGN KEY (hearing_id) 
    REFERENCES hearings(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_court FOREIGN KEY (court_type_id) 
    REFERENCES court_types(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_calendar_lawyer FOREIGN KEY (assigned_lawyer_id) 
    REFERENCES lawyers(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_calendar_creator FOREIGN KEY (created_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Cause List Imports (for CSV imports)
-- ====================
CREATE TABLE IF NOT EXISTS cause_list_imports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  import_date DATE NOT NULL,
  court_type_id INT NULL,
  court_level VARCHAR(50) NOT NULL,
  court_location VARCHAR(200) NOT NULL,
  file_name VARCHAR(500) NULL,
  total_cases INT NOT NULL DEFAULT 0,
  matched_cases INT NOT NULL DEFAULT 0,
  imported_by INT NOT NULL,
  imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  notes TEXT NULL,
  INDEX idx_date (import_date),
  INDEX idx_court (court_type_id),
  INDEX idx_imported_by (imported_by),
  CONSTRAINT fk_import_court FOREIGN KEY (court_type_id) 
    REFERENCES court_types(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_import_user FOREIGN KEY (imported_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Evidence Items
-- ====================
CREATE TABLE IF NOT EXISTS evidence_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  exhibit_label VARCHAR(50) NOT NULL COMMENT 'e.g., Exhibit A, Exhibit 1',
  description TEXT NOT NULL,
  evidence_type ENUM('Document', 'Physical Object', 'Digital File', 'Audio Recording', 'Video Recording', 'Photograph', 'Other') NOT NULL,
  file_path VARCHAR(500) NULL COMMENT 'Path to stored file if digital',
  file_hash VARCHAR(128) NULL COMMENT 'SHA-256 hash for integrity',
  file_size_bytes BIGINT NULL,
  file_mime_type VARCHAR(100) NULL,
  collection_date DATE NULL,
  collection_location VARCHAR(300) NULL,
  collected_by VARCHAR(200) NULL,
  current_state ENUM('Collected', 'Labeled', 'Stored', 'In Transit', 'Presented', 'Returned', 'Disposed') DEFAULT 'Collected',
  storage_location VARCHAR(300) NULL,
  is_sealed TINYINT(1) NOT NULL DEFAULT 0,
  seal_number VARCHAR(100) NULL,
  linked_hearing_id INT NULL,
  notes TEXT NULL,
  created_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_exhibit (exhibit_label),
  INDEX idx_state (current_state),
  INDEX idx_type (evidence_type),
  INDEX idx_hearing (linked_hearing_id),
  FULLTEXT idx_description (description),
  CONSTRAINT fk_evidence_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_evidence_hearing FOREIGN KEY (linked_hearing_id) 
    REFERENCES hearings(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_evidence_creator FOREIGN KEY (created_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Custody Events (Chain-of-Custody)
-- ====================
CREATE TABLE IF NOT EXISTS custody_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  evidence_id INT NOT NULL,
  event_type ENUM('Collected', 'Labeled', 'Transferred', 'Stored', 'Retrieved', 'Presented', 'Returned', 'Disposed', 'Photographed', 'Examined') NOT NULL,
  event_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  from_person VARCHAR(200) NULL COMMENT 'Person transferring custody',
  to_person VARCHAR(200) NULL COMMENT 'Person receiving custody',
  location VARCHAR(300) NULL,
  purpose TEXT NULL,
  witness_name VARCHAR(200) NULL,
  notes TEXT NULL,
  recorded_by INT NOT NULL,
  recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_evidence (evidence_id),
  INDEX idx_event_type (event_type),
  INDEX idx_event_date (event_date),
  INDEX idx_recorded_by (recorded_by),
  CONSTRAINT fk_custody_evidence FOREIGN KEY (evidence_id) 
    REFERENCES evidence_items(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_custody_recorder FOREIGN KEY (recorded_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Exhibit Lists (for court bundles export)
-- ====================
CREATE TABLE IF NOT EXISTS exhibit_lists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  list_name VARCHAR(200) NOT NULL,
  hearing_date DATE NULL,
  generated_for VARCHAR(200) NULL COMMENT 'e.g., Trial, Motion hearing',
  file_path VARCHAR(500) NULL COMMENT 'Path to generated PDF',
  evidence_items JSON NULL COMMENT 'Array of evidence IDs included',
  generated_by INT NOT NULL,
  generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_matter (matter_id),
  INDEX idx_hearing_date (hearing_date),
  CONSTRAINT fk_exhibit_list_matter FOREIGN KEY (matter_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_exhibit_list_generator FOREIGN KEY (generated_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Insert Ghana Court Types (Sample Data)
-- ====================
INSERT INTO court_types (court_level, location, court_name, is_active) VALUES
-- Supreme Court
('Supreme Court', 'Accra', 'Supreme Court of Ghana', 1),

-- Court of Appeal
('Court of Appeal', 'Accra', 'Court of Appeal - Accra', 1),
('Court of Appeal', 'Kumasi', 'Court of Appeal - Kumasi', 1),

-- High Courts
('High Court', 'Accra', 'High Court (Commercial Division) - Accra', 1),
('High Court', 'Accra', 'High Court (General Jurisdiction) - Accra', 1),
('High Court', 'Accra', 'High Court (Criminal Division) - Accra', 1),
('High Court', 'Kumasi', 'High Court - Kumasi', 1),
('High Court', 'Takoradi', 'High Court - Takoradi', 1),
('High Court', 'Tamale', 'High Court - Tamale', 1),
('High Court', 'Cape Coast', 'High Court - Cape Coast', 1),

-- Circuit Courts
('Circuit Court', 'Accra', 'Circuit Court - Accra', 1),
('Circuit Court', 'Tema', 'Circuit Court - Tema', 1),
('Circuit Court', 'Kumasi', 'Circuit Court - Kumasi', 1),
('Circuit Court', 'Takoradi', 'Circuit Court - Takoradi', 1),
('Circuit Court', 'Tamale', 'Circuit Court - Tamale', 1),

-- District Courts
('District Court', 'Accra', 'District Court - Accra', 1),
('District Court', 'Tema', 'District Court - Tema', 1),
('District Court', 'Kumasi', 'District Court - Kumasi', 1),
('District Court', 'Takoradi', 'District Court - Takoradi', 1),
('District Court', 'Tamale', 'District Court - Tamale', 1)
ON DUPLICATE KEY UPDATE id=id;

-- ====================
-- Evidence Type Reference Data
-- ====================
-- Evidence states follow chain: Collected → Labeled → Stored → In Transit → Presented → Returned → Disposed

-- ====================
-- Views for Quick Access
-- ====================

-- View: Today's Court Calendar
CREATE OR REPLACE VIEW vw_todays_calendar AS
SELECT 
  cc.id,
  cc.matter_id,
  c.matter_id as matter_number,
  c.title as matter_title,
  cc.court_level,
  cc.court_location,
  cc.court_room,
  cc.judge_name,
  cc.hearing_date,
  cc.hearing_time,
  cc.hearing_type,
  cc.status,
  cc.assigned_lawyer_id,
  CONCAT(e.first_name, ' ', e.surname) as lawyer_name,
  cl.name as client_name
FROM court_calendars cc
JOIN cases c ON c.id = cc.matter_id
JOIN lawyers l ON l.id = cc.assigned_lawyer_id
JOIN employees e ON e.id = l.employee_id
JOIN clients cl ON cl.id = c.client_id
WHERE cc.hearing_date = CURDATE()
  AND cc.status IN ('Scheduled', 'Confirmed')
ORDER BY cc.hearing_time ASC;

-- View: Evidence by Matter
CREATE OR REPLACE VIEW vw_matter_evidence AS
SELECT 
  ei.id,
  ei.matter_id,
  c.matter_id as matter_number,
  ei.exhibit_label,
  ei.description,
  ei.evidence_type,
  ei.current_state,
  ei.file_hash,
  ei.collection_date,
  ei.is_sealed,
  ei.storage_location,
  COUNT(ce.id) as custody_events_count
FROM evidence_items ei
JOIN cases c ON c.id = ei.matter_id
LEFT JOIN custody_events ce ON ce.evidence_id = ei.id
GROUP BY ei.id
ORDER BY ei.exhibit_label;

-- ====================
-- Indexes for Performance
-- ====================

-- Composite index for clash detection (same lawyer, overlapping time)
CREATE INDEX IF NOT EXISTS idx_clash_detection ON court_calendars (assigned_lawyer_id, hearing_date, hearing_time, status);

-- Composite index for court room scheduling
CREATE INDEX IF NOT EXISTS idx_room_schedule ON court_calendars (court_location, court_room, hearing_date, hearing_time);

-- Full-text search on evidence descriptions
-- Already created as FULLTEXT idx_description on evidence_items

-- ====================
-- Custody events will be recorded via application layer
-- Automatic triggers removed for migration compatibility
-- ====================