-- Law Firm ERP v2 - Dashboard Tables
-- Migration: 002 - Supporting tables for dashboard metrics

-- ====================
-- Lawyers Table
-- ====================
CREATE TABLE IF NOT EXISTS lawyers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  bar_number VARCHAR(50) UNIQUE NOT NULL,
  specialization VARCHAR(120) 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,
  INDEX idx_employee (employee_id),
  INDEX idx_active (is_active),
  CONSTRAINT fk_lawyers_employee FOREIGN KEY (employee_id) 
    REFERENCES employees(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Clients Table
-- ====================
CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_type ENUM('Individual', 'Corporate', 'Government', 'NGO') NOT NULL,
  name VARCHAR(200) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  address TEXT 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,
  INDEX idx_type (client_type),
  INDEX idx_active (is_active),
  INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Cases Table
-- ====================
CREATE TABLE IF NOT EXISTS cases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_number VARCHAR(50) UNIQUE NOT NULL,
  case_type ENUM('Civil', 'Criminal', 'Corporate', 'Family', 'Property', 'Labour') NOT NULL,
  case_status ENUM('Active', 'Pending', 'Closed', 'Won', 'Lost') NOT NULL DEFAULT 'Active',
  client_id INT NOT NULL,
  lawyer_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  filing_date DATE NOT NULL,
  closing_date DATE NULL,
  location VARCHAR(200) NULL,
  latitude DECIMAL(10, 8) NULL,
  longitude DECIMAL(11, 8) 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,
  INDEX idx_case_number (case_number),
  INDEX idx_type (case_type),
  INDEX idx_status (case_status),
  INDEX idx_client (client_id),
  INDEX idx_lawyer (lawyer_id),
  INDEX idx_dates (filing_date, closing_date),
  CONSTRAINT fk_cases_client FOREIGN KEY (client_id) 
    REFERENCES clients(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_cases_lawyer FOREIGN KEY (lawyer_id) 
    REFERENCES lawyers(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Invoices Table
-- ====================
CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(50) UNIQUE NOT NULL,
  case_id INT NOT NULL,
  client_id INT NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  paid_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  invoice_date DATE NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('Draft', 'Sent', 'Paid', 'Overdue', 'Cancelled') NOT NULL DEFAULT 'Draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_invoice_number (invoice_number),
  INDEX idx_case (case_id),
  INDEX idx_client (client_id),
  INDEX idx_status (status),
  INDEX idx_dates (invoice_date, due_date),
  CONSTRAINT fk_invoices_case FOREIGN KEY (case_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) 
    REFERENCES clients(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Expenses Table
-- ====================
CREATE TABLE IF NOT EXISTS expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NULL,
  category VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  expense_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_case (case_id),
  INDEX idx_category (category),
  INDEX idx_date (expense_date),
  CONSTRAINT fk_expenses_case FOREIGN KEY (case_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Hearings Table
-- ====================
CREATE TABLE IF NOT EXISTS hearings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  hearing_date DATETIME NOT NULL,
  location VARCHAR(200) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_case (case_id),
  INDEX idx_date (hearing_date),
  CONSTRAINT fk_hearings_case FOREIGN KEY (case_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Documents Table
-- ====================
CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  document_type VARCHAR(100) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_size INT NOT NULL,
  uploaded_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_case (case_id),
  INDEX idx_type (document_type),
  INDEX idx_uploaded (uploaded_by),
  CONSTRAINT fk_documents_case FOREIGN KEY (case_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Feedback Table
-- ====================
CREATE TABLE IF NOT EXISTS feedback (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  client_id INT NOT NULL,
  rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comments TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_case (case_id),
  INDEX idx_client (client_id),
  INDEX idx_rating (rating),
  CONSTRAINT fk_feedback_case FOREIGN KEY (case_id) 
    REFERENCES cases(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_feedback_client FOREIGN KEY (client_id) 
    REFERENCES clients(id) 
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;