-- INNENT SCHOOL360 | Migration 003: Academic structure (spec sec 19-21)
CREATE TABLE subjects (                         -- subject OR CBC learning area (kind), fully configurable
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NULL,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(120) NOT NULL,
  kind ENUM('subject','learning_area') NOT NULL DEFAULT 'learning_area',
  is_compulsory TINYINT(1) NOT NULL DEFAULT 1,
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uq_subject (school_id, code),
  UNIQUE KEY uq_subject_tenant (school_id, id),
  CONSTRAINT fk_sub_dept FOREIGN KEY (school_id, department_id) REFERENCES departments(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE strands (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_strand_tenant (school_id, id),
  CONSTRAINT fk_strand_sub FOREIGN KEY (school_id, subject_id) REFERENCES subjects(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE sub_strands (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL, strand_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL, sort_order SMALLINT NOT NULL DEFAULT 0,
  CONSTRAINT fk_ss_strand FOREIGN KEY (school_id, strand_id) REFERENCES strands(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE class_subjects (                   -- which subjects a class studies
  school_id INT UNSIGNED NOT NULL, class_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL,
  is_compulsory TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (class_id, subject_id),
  CONSTRAINT fk_cs_class FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id),
  CONSTRAINT fk_cs_sub FOREIGN KEY (school_id, subject_id) REFERENCES subjects(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE staff_profiles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
  employee_no VARCHAR(30) NOT NULL, tsc_no VARCHAR(30) NULL,
  department_id INT UNSIGNED NULL, joined_on DATE NULL, qualifications TEXT NULL,
  UNIQUE KEY uq_staff_user (school_id, user_id),
  UNIQUE KEY uq_staff_no (school_id, employee_no),
  CONSTRAINT fk_sp_user FOREIGN KEY (school_id, user_id) REFERENCES users(school_id, id),
  CONSTRAINT fk_sp_dept FOREIGN KEY (school_id, department_id) REFERENCES departments(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE class_teachers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL, academic_year_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL, stream_id INT UNSIGNED NULL, teacher_user_id BIGINT UNSIGNED NOT NULL,
  stream_key INT UNSIGNED GENERATED ALWAYS AS (IFNULL(stream_id,0)) STORED,
  UNIQUE KEY uq_ct (academic_year_id, class_id, stream_key),
  CONSTRAINT fk_ct_year FOREIGN KEY (school_id, academic_year_id) REFERENCES academic_years(school_id, id),
  CONSTRAINT fk_ct_class FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id),
  CONSTRAINT fk_ct_stream FOREIGN KEY (school_id, stream_id) REFERENCES streams(school_id, id),
  CONSTRAINT fk_ct_user FOREIGN KEY (school_id, teacher_user_id) REFERENCES users(school_id, id)
) ENGINE=InnoDB;

CREATE TABLE subject_teachers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL, academic_year_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL, stream_id INT UNSIGNED NULL, subject_id INT UNSIGNED NOT NULL,
  teacher_user_id BIGINT UNSIGNED NOT NULL,
  stream_key INT UNSIGNED GENERATED ALWAYS AS (IFNULL(stream_id,0)) STORED,
  UNIQUE KEY uq_st (academic_year_id, class_id, stream_key, subject_id),
  KEY idx_st_teacher (school_id, teacher_user_id),
  CONSTRAINT fk_st_year FOREIGN KEY (school_id, academic_year_id) REFERENCES academic_years(school_id, id),
  CONSTRAINT fk_st_class FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id),
  CONSTRAINT fk_st_stream FOREIGN KEY (school_id, stream_id) REFERENCES streams(school_id, id),
  CONSTRAINT fk_st_sub FOREIGN KEY (school_id, subject_id) REFERENCES subjects(school_id, id),
  CONSTRAINT fk_st_user FOREIGN KEY (school_id, teacher_user_id) REFERENCES users(school_id, id)
) ENGINE=InnoDB;

-- New permission, granted to templates AND every school's copied roles
INSERT INTO permissions (code, module, is_sensitive) VALUES ('academics.view','academics',0);
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN ('academics.view')
WHERE r.code IN ('school_admin','principal','bursar','class_teacher','subject_teacher');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code='academics.manage' WHERE r.code='principal';