-- 7. FEES
-- ---------------------------------------------------------------------

CREATE TABLE fee_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL               -- Tuition, PTA Levy, Uniform, Feeding...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fee_structures (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_level_id INT UNSIGNED NOT NULL,
  fee_item_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  is_mandatory TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fee_structure (class_level_id, fee_item_id, term_id, session_id),
  CONSTRAINT fk_fs_level FOREIGN KEY (class_level_id) REFERENCES class_levels(id),
  CONSTRAINT fk_fs_item FOREIGN KEY (fee_item_id) REFERENCES fee_items(id),
  CONSTRAINT fk_fs_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_fs_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fee_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  fee_structure_id INT UNSIGNED NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  payment_method ENUM('cash','bank_transfer','card','pos','ussd') NOT NULL,
  teller_no VARCHAR(50) DEFAULT NULL,
  payment_status ENUM('cleared','owing') NOT NULL DEFAULT 'owing',
  recorded_by BIGINT UNSIGNED NOT NULL,
  paid_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_fp_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_fp_structure FOREIGN KEY (fee_structure_id) REFERENCES fee_structures(id),
  CONSTRAINT fk_fp_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;