-- Qudurat AI - M1 schema
-- Target: MySQL 8.x / phpMyAdmin
-- Charset: utf8mb4

CREATE DATABASE IF NOT EXISTS qudurat_ai
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE qudurat_ai;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NULL UNIQUE,
  phone VARCHAR(30) NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  status ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
  email_verified_at TIMESTAMP NULL,
  last_login_at TIMESTAMP NULL,
  remember_token VARCHAR(100) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  group_name VARCHAR(100) NULL,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_permissions_group (group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_role (user_id, role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_role_permission (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  grade_level VARCHAR(60) NULL,
  target_score TINYINT UNSIGNED NULL,
  exam_date DATE NULL,
  daily_study_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  placement_score DECIMAL(5,2) NULL,
  quant_score DECIMAL(5,2) NULL,
  verbal_score DECIMAL(5,2) NULL,
  onboarding_completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_student_exam_target (exam_date, target_score),
  CONSTRAINT fk_student_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  preferred_report_frequency ENUM('daily','weekly','both','none') NOT NULL DEFAULT 'weekly',
  absence_alert_enabled TINYINT(1) NOT NULL DEFAULT 1,
  daily_report_enabled TINYINT(1) NOT NULL DEFAULT 0,
  weekly_report_enabled TINYINT(1) NOT NULL DEFAULT 1,
  report_email VARCHAR(190) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_parent_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_student_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_profile_id BIGINT UNSIGNED NOT NULL,
  student_profile_id BIGINT UNSIGNED NOT NULL,
  relationship VARCHAR(50) NOT NULL DEFAULT 'guardian',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_parent_student (parent_profile_id, student_profile_id),
  INDEX idx_student_primary_parent (student_profile_id, is_primary),
  CONSTRAINT fk_parent_student_parent FOREIGN KEY (parent_profile_id) REFERENCES parent_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_parent_student_student FOREIGN KEY (student_profile_id) REFERENCES student_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (name, slug, is_system) VALUES
('Student','student',1),
('Parent','parent',1),
('School Admin','school-admin',1),
('Content Reviewer','content-reviewer',1),
('Support Agent','support-agent',1),
('Super Admin','super-admin',1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO permissions (name, slug, group_name) VALUES
('View Student Dashboard','student.dashboard.view','student'),
('Use Training','training.use','training'),
('Use AI Tutor','ai.tutor.use','ai'),
('View Child Reports','parent.reports.view','parent'),
('Manage Parent Notifications','parent.notifications.manage','parent'),
('View Question Bank','questions.view','questions'),
('Create Questions','questions.create','questions'),
('Edit Questions','questions.edit','questions'),
('Approve Questions','questions.approve','questions'),
('Publish Questions','questions.publish','questions'),
('Import Questions','questions.import','questions'),
('Manage Users','users.manage','admin'),
('Manage Roles','roles.manage','admin'),
('View Audit Logs','audit.view','security')
ON DUPLICATE KEY UPDATE name=VALUES(name), group_name=VALUES(group_name);