-- ================================================
-- PHASE 8: Time, Billing & Trust Accounting
-- Migration: 005_phase8_time_billing_trust.sql
-- Created: November 25, 2025
-- ================================================

-- ================================================
-- 1. TIME TRACKING
-- ================================================

-- Activity Codes (Lookup Table)
CREATE TABLE IF NOT EXISTS activity_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description TEXT NULL,
  default_billable BOOLEAN DEFAULT TRUE,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_code (code),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Time Entries
CREATE TABLE IF NOT EXISTS time_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  matter_id INT NOT NULL,
  task_id INT NULL,
  entry_date DATE NOT NULL,
  start_time TIME NULL,
  end_time TIME NULL,
  hours DECIMAL(5,2) NOT NULL,
  activity_code VARCHAR(50) NOT NULL,
  description TEXT NOT NULL,
  billable BOOLEAN DEFAULT TRUE,
  hourly_rate DECIMAL(10,2) NULL,
  total_amount DECIMAL(10,2) NULL,
  status ENUM('Draft', 'Submitted', 'Approved', 'Rejected', 'Invoiced') DEFAULT 'Draft',
  submitted_at DATETIME NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  rejection_reason TEXT NULL,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_user_date (user_id, entry_date),
  INDEX idx_matter (matter_id),
  INDEX idx_status (status),
  INDEX idx_billable (billable, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Running Timers (Active Sessions)
CREATE TABLE IF NOT EXISTS running_timers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  matter_id INT NOT NULL,
  activity_code VARCHAR(50) NOT NULL,
  description TEXT NULL,
  start_time DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_timer (user_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 2. FEE ARRANGEMENTS
-- ================================================

-- Fee Arrangements
CREATE TABLE IF NOT EXISTS fee_arrangements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  fee_type ENUM('Hourly', 'Fixed', 'Capped', 'Contingency', 'Blended', 'Custom') NOT NULL,
  base_hourly_rate DECIMAL(10,2) NULL,
  fixed_amount DECIMAL(10,2) NULL,
  cap_amount DECIMAL(10,2) NULL,
  contingency_percent DECIMAL(5,2) NULL,
  billing_frequency ENUM('Weekly', 'Bi-weekly', 'Monthly', 'Milestone', 'On Completion') DEFAULT 'Monthly',
  payment_terms_days INT DEFAULT 30,
  description TEXT NULL,
  terms_document_path VARCHAR(500) NULL,
  effective_date DATE NOT NULL,
  end_date DATE NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_matter (matter_id),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Cards (Standard Rates)
CREATE TABLE IF NOT EXISTS rate_cards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  role_id INT NULL,
  practice_area VARCHAR(100) NULL,
  client_id INT NULL,
  hourly_rate DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'GHS',
  effective_date DATE NOT NULL,
  end_date DATE NULL,
  is_default BOOLEAN DEFAULT FALSE,
  notes TEXT NULL,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_user (user_id),
  INDEX idx_role (role_id),
  INDEX idx_client (client_id),
  INDEX idx_effective (effective_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 3. EXPENSE MANAGEMENT
-- ================================================

-- Expense Categories (Lookup Table)
CREATE TABLE IF NOT EXISTS expense_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT NULL,
  default_billable BOOLEAN DEFAULT TRUE,
  default_markup_percent DECIMAL(5,2) DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expense Entries
CREATE TABLE IF NOT EXISTS expense_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL,
  user_id INT NOT NULL,
  expense_date DATE NOT NULL,
  category VARCHAR(100) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'GHS',
  description TEXT NOT NULL,
  receipt_path VARCHAR(500) NULL,
  receipt_number VARCHAR(100) NULL,
  vendor_name VARCHAR(200) NULL,
  billable BOOLEAN DEFAULT TRUE,
  markup_percent DECIMAL(5,2) DEFAULT 0,
  billable_amount DECIMAL(10,2) NULL,
  reimbursable BOOLEAN DEFAULT FALSE,
  status ENUM('Draft', 'Submitted', 'Approved', 'Rejected', 'Invoiced', 'Reimbursed') DEFAULT 'Draft',
  submitted_at DATETIME NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  rejection_reason TEXT NULL,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_matter (matter_id),
  INDEX idx_user (user_id),
  INDEX idx_status (status),
  INDEX idx_billable (billable, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 4. INVOICING
-- ================================================

-- Invoices
CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(50) NOT NULL UNIQUE,
  matter_id INT NOT NULL,
  client_id INT NOT NULL,
  invoice_type ENUM('Pro-forma', 'Tax Invoice', 'Interim', 'Final', 'Credit Note') DEFAULT 'Tax Invoice',
  invoice_date DATE NOT NULL,
  due_date DATE NOT NULL,
  period_start DATE NULL,
  period_end DATE NULL,
  status ENUM('Draft', 'Pending Approval', 'Approved', 'Issued', 'Partially Paid', 'Paid', 'Overdue', 'Written Off', 'Cancelled') DEFAULT 'Draft',
  
  -- Amounts
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  discount_reason TEXT NULL,
  
  -- Ghana Taxes
  vat_rate DECIMAL(5,2) DEFAULT 12.50,
  vat_amount DECIMAL(10,2) DEFAULT 0,
  nhil_rate DECIMAL(5,2) DEFAULT 2.50,
  nhil_amount DECIMAL(10,2) DEFAULT 0,
  getfund_rate DECIMAL(5,2) DEFAULT 2.50,
  getfund_amount DECIMAL(10,2) DEFAULT 0,
  
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(10,2) DEFAULT 0,
  balance_due DECIMAL(10,2) DEFAULT 0,
  
  -- Withholding Tax (corporate clients)
  wht_applicable BOOLEAN DEFAULT FALSE,
  wht_rate DECIMAL(5,2) DEFAULT 0,
  wht_amount DECIMAL(10,2) DEFAULT 0,
  
  -- Metadata
  currency VARCHAR(3) DEFAULT 'GHS',
  notes TEXT NULL,
  terms TEXT NULL,
  pdf_path VARCHAR(500) NULL,
  
  issued_date DATE NULL,
  issued_by INT NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
  FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  
  INDEX idx_invoice_number (invoice_number),
  INDEX idx_matter (matter_id),
  INDEX idx_client (client_id),
  INDEX idx_status (status),
  INDEX idx_due_date (due_date),
  INDEX idx_invoice_date (invoice_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoice Line Items
CREATE TABLE IF NOT EXISTS invoice_line_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  line_type ENUM('Time', 'Expense', 'Fee', 'Adjustment', 'Other') NOT NULL,
  time_entry_id INT NULL,
  expense_entry_id INT NULL,
  description TEXT NOT NULL,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
  rate DECIMAL(10,2) NOT NULL DEFAULT 0,
  amount DECIMAL(10,2) NOT NULL,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (time_entry_id) REFERENCES time_entries(id) ON DELETE SET NULL,
  FOREIGN KEY (expense_entry_id) REFERENCES expense_entries(id) ON DELETE SET NULL,
  INDEX idx_invoice (invoice_id),
  INDEX idx_time_entry (time_entry_id),
  INDEX idx_expense_entry (expense_entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 5. RECEIPTS & COLLECTIONS
-- ================================================

-- Receipts
CREATE TABLE IF NOT EXISTS receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  receipt_number VARCHAR(50) NOT NULL UNIQUE,
  client_id INT NOT NULL,
  receipt_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'GHS',
  payment_method ENUM('Cash', 'Bank Transfer', 'Mobile Money', 'Cheque', 'Credit Card', 'Online Payment', 'Other') NOT NULL,
  
  -- Payment Details
  reference_number VARCHAR(200) NULL,
  bank_name VARCHAR(200) NULL,
  mobile_network VARCHAR(50) NULL,
  mobile_number VARCHAR(20) NULL,
  cheque_number VARCHAR(50) NULL,
  cheque_date DATE NULL,
  
  -- Allocation
  allocated_amount DECIMAL(10,2) DEFAULT 0,
  unallocated_amount DECIMAL(10,2) DEFAULT 0,
  
  notes TEXT NULL,
  pdf_path VARCHAR(500) NULL,
  
  recorded_by INT NOT NULL,
  recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE RESTRICT,
  
  INDEX idx_receipt_number (receipt_number),
  INDEX idx_client (client_id),
  INDEX idx_receipt_date (receipt_date),
  INDEX idx_payment_method (payment_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Allocations
CREATE TABLE IF NOT EXISTS payment_allocations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  receipt_id INT NOT NULL,
  invoice_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  allocation_date DATE NOT NULL,
  notes TEXT NULL,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE RESTRICT,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_receipt (receipt_id),
  INDEX idx_invoice (invoice_id),
  UNIQUE KEY unique_receipt_invoice (receipt_id, invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Collection Activities
CREATE TABLE IF NOT EXISTS collection_activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  activity_date DATE NOT NULL,
  activity_type ENUM('Email', 'Phone Call', 'SMS', 'WhatsApp', 'Letter', 'Meeting', 'Other') NOT NULL,
  notes TEXT NOT NULL,
  next_action_date DATE NULL,
  performed_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_invoice (invoice_id),
  INDEX idx_activity_date (activity_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 6. TRUST ACCOUNTING
-- ================================================

-- Trust Accounts (Per Client)
CREATE TABLE IF NOT EXISTS trust_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL UNIQUE,
  account_number VARCHAR(50) NOT NULL UNIQUE,
  balance DECIMAL(15,2) NOT NULL DEFAULT 0,
  currency VARCHAR(3) DEFAULT 'GHS',
  status ENUM('Active', 'Frozen', 'Closed') DEFAULT 'Active',
  opened_date DATE NOT NULL,
  closed_date DATE NULL,
  last_transaction_date DATE NULL,
  last_reconciliation_date DATE NULL,
  notes TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
  INDEX idx_client (client_id),
  INDEX idx_account_number (account_number),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Trust Transactions (Immutable Ledger)
CREATE TABLE IF NOT EXISTS trust_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trust_account_id INT NOT NULL,
  transaction_number VARCHAR(50) NOT NULL UNIQUE,
  transaction_date DATE NOT NULL,
  transaction_type ENUM('Deposit', 'Withdrawal', 'Transfer to Operating', 'Transfer from Operating', 'Refund', 'Adjustment') NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  balance_after DECIMAL(15,2) NOT NULL,
  
  -- Related References
  matter_id INT NULL,
  invoice_id INT NULL,
  expense_id INT NULL,
  
  description TEXT NOT NULL,
  reference_number VARCHAR(200) NULL,
  payment_method VARCHAR(50) NULL,
  
  -- Dual Control
  initiated_by INT NOT NULL,
  initiated_at DATETIME NOT NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  approval_notes TEXT NULL,
  
  status ENUM('Pending', 'Approved', 'Rejected', 'Cancelled') DEFAULT 'Pending',
  
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (trust_account_id) REFERENCES trust_accounts(id) ON DELETE RESTRICT,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE SET NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  FOREIGN KEY (initiated_by) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  
  INDEX idx_trust_account (trust_account_id),
  INDEX idx_transaction_number (transaction_number),
  INDEX idx_transaction_date (transaction_date),
  INDEX idx_status (status),
  INDEX idx_matter (matter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Trust Reconciliation Records
CREATE TABLE IF NOT EXISTS trust_reconciliations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reconciliation_date DATE NOT NULL,
  bank_statement_balance DECIMAL(15,2) NOT NULL,
  ledger_balance DECIMAL(15,2) NOT NULL,
  outstanding_deposits DECIMAL(15,2) DEFAULT 0,
  outstanding_withdrawals DECIMAL(15,2) DEFAULT 0,
  reconciled_balance DECIMAL(15,2) NOT NULL,
  variance DECIMAL(15,2) DEFAULT 0,
  variance_notes TEXT NULL,
  statement_document_path VARCHAR(500) NULL,
  performed_by INT NOT NULL,
  reviewed_by INT NULL,
  reviewed_at DATETIME NULL,
  status ENUM('Draft', 'Complete', 'Approved') DEFAULT 'Draft',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_reconciliation_date (reconciliation_date),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- 7. WIP & REVENUE RECOGNITION
-- ================================================

-- WIP Summary (Materialized View / Computed)
CREATE TABLE IF NOT EXISTS wip_summary (
  id INT AUTO_INCREMENT PRIMARY KEY,
  matter_id INT NOT NULL UNIQUE,
  unbilled_time_hours DECIMAL(10,2) DEFAULT 0,
  unbilled_time_amount DECIMAL(10,2) DEFAULT 0,
  unbilled_expense_amount DECIMAL(10,2) DEFAULT 0,
  total_wip_amount DECIMAL(10,2) DEFAULT 0,
  last_invoice_date DATE NULL,
  last_invoice_amount DECIMAL(10,2) DEFAULT 0,
  aging_days INT DEFAULT 0,
  last_calculated_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE CASCADE,
  INDEX idx_matter (matter_id),
  INDEX idx_aging (aging_days)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Revenue Recognition
CREATE TABLE IF NOT EXISTS revenue_recognition (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_month DATE NOT NULL,
  matter_id INT NOT NULL,
  partner_id INT NOT NULL,
  practice_area VARCHAR(100) NULL,
  
  -- Revenue Metrics
  billed_amount DECIMAL(10,2) DEFAULT 0,
  collected_amount DECIMAL(10,2) DEFAULT 0,
  written_off_amount DECIMAL(10,2) DEFAULT 0,
  writedown_amount DECIMAL(10,2) DEFAULT 0,
  
  -- Time Metrics
  billable_hours DECIMAL(10,2) DEFAULT 0,
  billed_hours DECIMAL(10,2) DEFAULT 0,
  
  -- Partner Credits
  origination_credit DECIMAL(5,2) DEFAULT 100.00,
  execution_credit DECIMAL(5,2) DEFAULT 0,
  
  notes TEXT NULL,
  calculated_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (matter_id) REFERENCES matters(id) ON DELETE RESTRICT,
  FOREIGN KEY (partner_id) REFERENCES users(id) ON DELETE RESTRICT,
  
  INDEX idx_period (period_month),
  INDEX idx_matter (matter_id),
  INDEX idx_partner (partner_id),
  UNIQUE KEY unique_period_matter (period_month, matter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- SEED DATA
-- ================================================

-- Activity Codes
INSERT INTO activity_codes (code, name, description, default_billable, sort_order) VALUES
('RESEARCH', 'Legal Research', 'Research of case law, statutes, and regulations', TRUE, 1),
('DRAFT', 'Drafting', 'Drafting pleadings, contracts, and other documents', TRUE, 2),
('REVIEW', 'Document Review', 'Review of documents and evidence', TRUE, 3),
('MEETING-CLIENT', 'Client Meeting', 'Meetings with clients', TRUE, 4),
('MEETING-COURT', 'Court Appearance', 'Attendance at court hearings', TRUE, 5),
('MEETING-OPP', 'Opposing Counsel Meeting', 'Meetings with opposing counsel', TRUE, 6),
('STRATEGY', 'Case Strategy', 'Strategy planning and case analysis', TRUE, 7),
('CORRESPONDENCE', 'Correspondence', 'Letters, emails, and communications', TRUE, 8),
('TRAVEL', 'Travel Time', 'Travel to/from court or client meetings', TRUE, 9),
('ADMIN', 'Administrative', 'File management and administrative tasks', FALSE, 10),
('INTERNAL', 'Internal Meeting', 'Firm meetings and training', FALSE, 11);

-- Expense Categories
INSERT INTO expense_categories (category_name, description, default_billable, default_markup_percent, sort_order) VALUES
('Court Fees', 'Filing fees and court costs', TRUE, 0, 1),
('Service of Process', 'Process server and service fees', TRUE, 10, 2),
('Expert Witness', 'Expert witness fees and consultation', TRUE, 0, 3),
('Travel', 'Transportation and accommodation', TRUE, 0, 4),
('Photocopying', 'Document reproduction and printing', TRUE, 15, 5),
('Research', 'Legal databases and research materials', TRUE, 10, 6),
('Courier', 'Courier and delivery services', TRUE, 10, 7),
('Translation', 'Document translation services', TRUE, 0, 8),
('Telephone', 'Long distance and international calls', TRUE, 0, 9),
('Filing', 'Document filing and storage', TRUE, 0, 10),
('Office Supplies', 'Stationery and supplies for matter', TRUE, 20, 11),
('Other', 'Other billable expenses', TRUE, 0, 12);

-- ====================
-- MIGRATION COMPLETE
-- ====================
-- Phase 8A: Database Schema
-- 17 tables created:
--   - Time Tracking: activity_codes, time_entries, running_timers
--   - Fee Arrangements: fee_arrangements, rate_cards
--   - Expenses: expense_categories, expense_entries
--   - Invoicing: invoices, invoice_line_items
--   - Receipts: receipts, payment_allocations, collection_activities
--   - Trust: trust_accounts, trust_transactions, trust_reconciliations
--   - WIP/Revenue: wip_summary, revenue_recognition
-- Seed data: 11 activity codes, 12 expense categories
-- ====================