-- ระบบบริหารสำนักงานทนายความ.com
-- Hostinger-compatible update: Import หลังเลือกฐานข้อมูล u130072803_lawfirm แล้ว
-- ไม่สร้างฐานข้อมูลใหม่ ไม่เปลี่ยนฐานข้อมูล และไม่ลบข้อมูลเดิม
-- MySQL 8.0+ / MariaDB 10.6+

CREATE TABLE IF NOT EXISTS offices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  office_code VARCHAR(30) NOT NULL UNIQUE,
  office_name VARCHAR(190) NOT NULL,
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  office_id BIGINT UNSIGNED NULL,
  role_code ENUM('super_admin','office_head','lawyer','legal_assistant','law_clerk','accounting') NOT NULL,
  full_name VARCHAR(190) NOT NULL,
  mobile VARCHAR(20) NULL UNIQUE,
  username VARCHAR(100) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('pending','active','suspended','disabled') NOT NULL DEFAULT 'pending',
  approved_at DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_office FOREIGN KEY (office_id) REFERENCES offices(id) ON DELETE RESTRICT,
  CONSTRAINT chk_login_identity CHECK (mobile IS NOT NULL OR username IS NOT NULL),
  INDEX idx_users_office_role (office_id, role_code),
  INDEX idx_users_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS login_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  identity_value VARCHAR(190) NOT NULL,
  ip_address VARCHAR(45) NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_login_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_login_audit_time (attempted_at),
  INDEX idx_login_audit_identity (identity_value)
) ENGINE=InnoDB;

-- ห้ามใส่รหัสผ่านจริงในไฟล์ SQL นี้
-- สร้าง password_hash ด้วย PHP: password_hash($password, PASSWORD_DEFAULT)