-- Law Firm ERP v2 - Initial Database Schema
-- Migration: 001 - Core Tables

-- ====================
-- Departments Table
-- ====================
CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  description 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_active (is_active),
  INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Roles Table
-- ====================
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  department_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  description TEXT NOT 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,
  UNIQUE KEY uq_role_dept (department_id, name),
  INDEX idx_dept (department_id),
  INDEX idx_active (is_active),
  CONSTRAINT fk_roles_department FOREIGN KEY (department_id) 
    REFERENCES departments(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Employees Table
-- ====================
CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_number VARCHAR(50) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  middle_name VARCHAR(100) NULL,
  surname VARCHAR(100) NOT NULL,
  dob DATE NOT NULL,
  phone VARCHAR(40) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  address TEXT NOT NULL,
  department_id INT NOT NULL,
  role_id INT NOT 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_number (employee_number),
  INDEX idx_email (email),
  INDEX idx_dept (department_id),
  INDEX idx_role (role_id),
  INDEX idx_active (is_active),
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) 
    REFERENCES departments(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_emp_role FOREIGN KEY (role_id) 
    REFERENCES roles(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Users Table
-- ====================
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  username VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  department_id INT NOT NULL,
  role_id INT NOT 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_username (username),
  INDEX idx_employee (employee_id),
  INDEX idx_active (is_active),
  CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) 
    REFERENCES employees(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_users_dept FOREIGN KEY (department_id) 
    REFERENCES departments(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) 
    REFERENCES roles(id) 
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Sessions Table (for cookie-based auth)
-- ====================
CREATE TABLE IF NOT EXISTS sessions (
  id CHAR(36) PRIMARY KEY,
  user_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME NULL,
  expires_at DATETIME NOT NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  INDEX idx_user (user_id),
  INDEX idx_expires (expires_at),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) 
    REFERENCES users(id) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Audit Logs Table
-- ====================
CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  entity VARCHAR(64) NOT NULL,
  entity_id INT NOT NULL,
  action ENUM('CREATE','READ','UPDATE','DELETE','LOGIN','LOGOUT') NOT NULL,
  meta JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_entity (entity, entity_id),
  INDEX idx_user (user_id),
  INDEX idx_action (action),
  INDEX idx_created (created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) 
    REFERENCES users(id) 
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================
-- Users Listing View
-- ====================
CREATE OR REPLACE VIEW v_users_listing AS
SELECT 
  u.id,
  CONCAT(e.first_name, ' ', IFNULL(CONCAT(e.middle_name, ' '), ''), e.surname) AS employee_name,
  u.username,
  d.name AS department_name,
  r.name AS role_name,
  u.is_active,
  u.created_at,
  u.updated_at
FROM users u
JOIN employees e ON e.id = u.employee_id
JOIN departments d ON d.id = u.department_id
JOIN roles r ON r.id = u.role_id;