CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role_id INT NOT NULL,
  display_name VARCHAR(255) NOT NULL DEFAULT '',
  phone VARCHAR(50) NULL,
  avatar_storage_key VARCHAR(512) NULL,
  avatar_thumbnail_key VARCHAR(512) NULL,
  timezone VARCHAR(100) NULL DEFAULT 'Asia/Karachi',
  locale VARCHAR(20) NULL DEFAULT 'en-US',
  status ENUM('active', 'inactive', 'locked') NOT NULL DEFAULT 'active',
  failed_login_attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  client_id INT NULL,
  employee_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE IF NOT EXISTS refresh_tokens (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_refresh_user (user_id),
  INDEX idx_refresh_hash (token_hash)
);

CREATE TABLE IF NOT EXISTS app_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  section VARCHAR(50) NOT NULL UNIQUE,
  payload JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by_user_id INT NULL,
  CONSTRAINT fk_settings_user FOREIGN KEY (updated_by_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  user_email VARCHAR(255) NULL,
  role_name VARCHAR(50) NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id VARCHAR(80) NULL,
  page_path VARCHAR(512) NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(512) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_created (created_at),
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_entity (entity_type, entity_id)
);

CREATE TABLE IF NOT EXISTS trash_items (
  id CHAR(36) PRIMARY KEY,
  entity_type VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NOT NULL,
  title VARCHAR(512) NOT NULL,
  subtitle VARCHAR(512) NULL,
  snapshot JSON NOT NULL,
  parent_ref JSON NULL,
  deleted_at DATETIME NOT NULL,
  purge_at DATETIME NOT NULL,
  deleted_by_user_id INT NULL,
  deleted_by_email VARCHAR(255) NULL,
  deleted_by_role VARCHAR(50) NULL,
  permanent TINYINT NOT NULL DEFAULT 0,
  INDEX idx_trash_purge (purge_at),
  INDEX idx_trash_entity (entity_type)
);

CREATE TABLE IF NOT EXISTS file_assets (
  id CHAR(36) PRIMARY KEY,
  owner_user_id INT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NOT NULL,
  original_key VARCHAR(512) NOT NULL,
  thumbnail_key VARCHAR(512) NULL,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  original_name VARCHAR(512) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_file_owner FOREIGN KEY (owner_user_id) REFERENCES users(id),
  INDEX idx_file_entity (entity_type, entity_id)
);

CREATE TABLE IF NOT EXISTS notification_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_key VARCHAR(120) NOT NULL UNIQUE,
  label VARCHAR(255) NOT NULL,
  category VARCHAR(50) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  channels JSON NOT NULL,
  roles JSON NOT NULL
);

CREATE TABLE IF NOT EXISTS user_notifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  read_at DATETIME NULL,
  channel VARCHAR(50) NOT NULL DEFAULT 'websocket',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user (user_id)
);

CREATE TABLE IF NOT EXISTS schema_migrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL UNIQUE,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);