-- ============================================
-- MakLinkApp – database/schema.sql
-- Full database schema for TFSBD Platform
-- ============================================

CREATE DATABASE IF NOT EXISTS maklinkapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE maklinkapp;

-- ---- Users ----
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120)  NOT NULL,
  email         VARCHAR(180)  NOT NULL UNIQUE,
  username      VARCHAR(80)   NOT NULL UNIQUE,
  password_hash VARCHAR(255)  NOT NULL,
  role          ENUM('admin','manager','analyst','developer','viewer','correspondent') NOT NULL DEFAULT 'viewer',
  active        TINYINT(1)    NOT NULL DEFAULT 1,
  mfa_enabled   TINYINT(1)    NOT NULL DEFAULT 0,
  otp_code      VARCHAR(10)   NULL,
  otp_expires   DATETIME      NULL,
  language      ENUM('en','bn','bi') NOT NULL DEFAULT 'en',
  dark_mode     TINYINT(1)    NOT NULL DEFAULT 0,
  avatar_url    VARCHAR(512)  NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role  (role)
);

-- ---- Apps (launchpad tiles) ----
CREATE TABLE IF NOT EXISTS apps (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(80)   NOT NULL UNIQUE,
  name        VARCHAR(120)  NOT NULL,
  name_bn     VARCHAR(120)  NULL COMMENT 'Bangla name',
  description TEXT          NULL,
  icon        VARCHAR(512)  NULL,
  color       VARCHAR(10)   NULL DEFAULT '#E6F1FB',
  category    VARCHAR(60)   NULL,
  url         VARCHAR(512)  NULL,
  status      ENUM('live','development','disabled') NOT NULL DEFAULT 'development',
  sort_order  SMALLINT      NOT NULL DEFAULT 0,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ---- User-App assignments ----
CREATE TABLE IF NOT EXISTS user_apps (
  user_id     INT UNSIGNED NOT NULL,
  app_id      INT UNSIGNED NOT NULL,
  pinned      TINYINT(1)   NOT NULL DEFAULT 0,
  last_opened DATETIME     NULL,
  PRIMARY KEY (user_id, app_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (app_id)  REFERENCES apps(id)  ON DELETE CASCADE
);

-- ---- Classes & Roster ----
CREATE TABLE IF NOT EXISTS classes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120)  NOT NULL,
  code        VARCHAR(40)   NULL UNIQUE,
  manager_id  INT UNSIGNED  NULL,
  status      ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS class_members (
  class_id    INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  enrolled_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (class_id, user_id),
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)  REFERENCES users(id)   ON DELETE CASCADE
);

-- ---- Files ----
CREATE TABLE IF NOT EXISTS files (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED  NOT NULL,
  app_id      INT UNSIGNED  NULL,
  name        VARCHAR(255)  NOT NULL,
  mime_type   VARCHAR(100)  NULL,
  size_bytes  BIGINT        NOT NULL DEFAULT 0,
  storage_key VARCHAR(512)  NOT NULL COMMENT 'S3/cPanel path',
  encrypted   TINYINT(1)    NOT NULL DEFAULT 1,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (app_id)  REFERENCES apps(id)  ON DELETE SET NULL
);

-- ---- Activity log ----
CREATE TABLE IF NOT EXISTS activity_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED  NULL,
  app_id      INT UNSIGNED  NULL,
  action      VARCHAR(120)  NOT NULL,
  detail      TEXT          NULL,
  ip          VARCHAR(45)   NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_log (user_id, created_at),
  INDEX idx_app_log  (app_id,  created_at)
);

-- ---- Login log (security) ----
CREATE TABLE IF NOT EXISTS login_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED  NULL,
  ip          VARCHAR(45)   NULL,
  action      VARCHAR(60)   NOT NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_user (user_id),
  INDEX idx_login_ip   (ip)
);

-- ---- Notifications ----
CREATE TABLE IF NOT EXISTS notifications (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED  NOT NULL,
  type        VARCHAR(60)   NOT NULL,
  title       VARCHAR(200)  NOT NULL,
  body        TEXT          NULL,
  read_at     DATETIME      NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user (user_id, read_at)
);
