CREATE TABLE IF NOT EXISTS office_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(160) NOT NULL,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(40) NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_clients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  client_type VARCHAR(80) DEFAULT '',
  phone VARCHAR(80) DEFAULT '',
  email VARCHAR(160) DEFAULT '',
  address VARCHAR(255) DEFAULT '',
  status VARCHAR(60) DEFAULT 'Active',
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_clients_name (full_name),
  INDEX idx_clients_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_matters (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  lead_attorney_id INT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  matter_type VARCHAR(120) DEFAULT '',
  status VARCHAR(80) DEFAULT 'Open',
  risk_level VARCHAR(20) DEFAULT 'Green',
  ethical_wall VARCHAR(20) DEFAULT 'No',
  restricted_access VARCHAR(20) DEFAULT 'No',
  billing_status VARCHAR(80) DEFAULT 'Billable',
  court VARCHAR(160) DEFAULT '',
  case_number VARCHAR(120) DEFAULT '',
  opened_on DATE NULL,
  deadline_on DATE NULL,
  description TEXT,
  outcome TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_matters_client (client_id),
  INDEX idx_matters_attorney (lead_attorney_id),
  INDEX idx_matters_status (status),
  INDEX idx_matters_risk (risk_level),
  INDEX idx_matters_deadline (deadline_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_time_entries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  matter_id INT UNSIGNED NULL,
  document_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  entry_date DATE NULL,
  started_at DATETIME NULL,
  activity_type VARCHAR(120) DEFAULT '',
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  billable_status VARCHAR(80) DEFAULT 'Billable',
  hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  description TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_time_client (client_id),
  INDEX idx_time_matter (matter_id),
  INDEX idx_time_document (document_id),
  INDEX idx_time_user (user_id),
  INDEX idx_time_entry_date (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_audit_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id INT UNSIGNED NULL,
  detail VARCHAR(255) DEFAULT '',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_entity (entity_type, entity_id),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_appointments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  matter_id INT UNSIGNED NULL,
  requester_name VARCHAR(180) DEFAULT '',
  requester_phone VARCHAR(80) DEFAULT '',
  requester_email VARCHAR(180) DEFAULT '',
  service_requested VARCHAR(180) DEFAULT '',
  title VARCHAR(220) NOT NULL,
  appointment_at DATETIME NULL,
  location VARCHAR(180) DEFAULT '',
  status VARCHAR(80) DEFAULT 'Scheduled',
  approved_at DATETIME NULL,
  approval_email_sent_at DATETIME NULL,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_appointments_client (client_id),
  INDEX idx_appointments_matter (matter_id),
  INDEX idx_appointments_at (appointment_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  matter_id INT UNSIGNED NULL,
  assigned_to VARCHAR(160) DEFAULT '',
  title VARCHAR(220) NOT NULL,
  due_date DATE NULL,
  priority VARCHAR(40) DEFAULT 'Medium',
  status VARCHAR(80) DEFAULT 'Pending',
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tasks_matter (matter_id),
  INDEX idx_tasks_due (due_date),
  INDEX idx_tasks_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  matter_id INT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  document_type VARCHAR(120) DEFAULT '',
  file_url VARCHAR(255) DEFAULT '',
  signed_on DATE NULL,
  expires_on DATE NULL,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_documents_client (client_id),
  INDEX idx_documents_matter (matter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  matter_id INT UNSIGNED NULL,
  invoice_number VARCHAR(80) DEFAULT '',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency VARCHAR(12) DEFAULT 'USD',
  status VARCHAR(80) DEFAULT 'Draft',
  issued_on DATE NULL,
  due_on DATE NULL,
  notes TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_invoices_client (client_id),
  INDEX idx_invoices_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS office_notes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  matter_id INT UNSIGNED NULL,
  note_text TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_notes_client (client_id),
  INDEX idx_notes_matter (matter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;