-- Use utf8mb4 by default
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- USERS & AUTH
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  father_name VARCHAR(150),
  email VARCHAR(254),
  phone VARCHAR(20),
  email_verified_at DATETIME,
  phone_verified_at DATETIME,
  status ENUM('active','blocked','deleted') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_phone (phone)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT INTO roles (code) VALUES
  ('admin'),('staff'),('lawyer'),('broker'),('client');

CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE auth_identities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  provider ENUM('local','google','apple') NOT NULL,
  provider_user_id VARCHAR(191) NOT NULL,
  password_hash VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_auth_provider (provider, provider_user_id),
  KEY idx_auth_user (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE pan_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  pan_hash VARBINARY(32) NOT NULL,
  pan_last4 CHAR(4) NOT NULL,
  status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  verified_by_user_id BIGINT UNSIGNED,
  verified_at DATETIME,
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pan_user (user_id),
  KEY idx_pan_status (status),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (verified_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ORGS / TEAMS
CREATE TABLE organizations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  org_type ENUM('law_firm','broker','internal') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE organization_members (
  org_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  member_role ENUM('owner','member','staff') NOT NULL,
  status ENUM('active','invited','inactive') NOT NULL DEFAULT 'active',
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (org_id, user_id),
  KEY idx_org_members_user (user_id),
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- CASES
CREATE TABLE cases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  created_by_role ENUM('client','broker','staff','admin') NOT NULL,
  section_code VARCHAR(32),
  court_type VARCHAR(64),
  court_name VARCHAR(128),
  court_city VARCHAR(64),
  filing_date DATE,
  case_link TEXT,
  description TEXT,
  status ENUM('reported','assigned','accepted','rejected','verified','closed') NOT NULL DEFAULT 'reported',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cases_creator (created_by_user_id),
  KEY idx_cases_status (status),
  FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE case_parties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id BIGINT UNSIGNED NOT NULL,
  party_type ENUM('complainant','defendant') NOT NULL,
  user_id BIGINT UNSIGNED,
  full_name VARCHAR(150) NOT NULL,
  father_name VARCHAR(150),
  email VARCHAR(254),
  phone VARCHAR(20),
  address TEXT,
  city VARCHAR(64),
  pan_hash VARBINARY(32),
  pan_last4 CHAR(4),
  pan_verified_status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  KEY idx_case_parties_case (case_id),
  KEY idx_case_parties_type (case_id, party_type),
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE case_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id BIGINT UNSIGNED NOT NULL,
  uploaded_by_user_id BIGINT UNSIGNED NOT NULL,
  doc_type ENUM('case_scan','certificate','other') NOT NULL,
  storage_key VARCHAR(512) NOT NULL,
  file_name VARCHAR(255),
  mime_type VARCHAR(100),
  file_size BIGINT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_case_docs_case (case_id),
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE case_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id BIGINT UNSIGNED NOT NULL,
  lawyer_id BIGINT UNSIGNED NOT NULL,
  assigned_by_user_id BIGINT UNSIGNED NOT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('assigned','accepted','rejected','reassigned') NOT NULL DEFAULT 'assigned',
  responded_at DATETIME,
  reject_reason TEXT,
  KEY idx_case_assign_case (case_id),
  KEY idx_case_assign_lawyer (lawyer_id),
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (lawyer_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (assigned_by_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE lawyer_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id BIGINT UNSIGNED NOT NULL,
  lawyer_id BIGINT UNSIGNED NOT NULL,
  verification_status ENUM('positive','negative') NOT NULL,
  verified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note_for_admin TEXT,
  note_for_client TEXT,
  certificate_document_id BIGINT UNSIGNED,
  UNIQUE KEY uq_lawyer_verif (case_id, lawyer_id),
  KEY idx_lawyer_verif_case (case_id),
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (lawyer_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (certificate_document_id) REFERENCES case_documents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE blocked_identities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED,
  pan_hash VARBINARY(32),
  reason TEXT,
  blocked_by_user_id BIGINT UNSIGNED,
  blocked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (blocked_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CHECK (user_id IS NOT NULL OR pan_hash IS NOT NULL)
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED,
  channel ENUM('email','sms','whatsapp') NOT NULL,
  template_key VARCHAR(100),
  payload JSON,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  sent_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notify_user (user_id),
  KEY idx_notify_status (status),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE case_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  case_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(32),
  to_status VARCHAR(32) NOT NULL,
  changed_by_user_id BIGINT UNSIGNED,
  note TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_case_hist_case (case_id),
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (changed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Refresh tokens (JWT rotation)
CREATE TABLE refresh_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME,
  replaced_by_token_id BIGINT UNSIGNED,
  UNIQUE KEY uq_refresh_token_hash (token_hash),
  KEY idx_refresh_user (user_id),
  KEY idx_refresh_expires (expires_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (replaced_by_token_id) REFERENCES refresh_tokens(id) ON DELETE SET NULL
) ENGINE=InnoDB;
