-- Module toggle infrastructure
USE sts_v2;

-- Catalog of feature modules that can be toggled or gated behind subscriptions
CREATE TABLE IF NOT EXISTS modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  is_toggleable TINYINT(1) DEFAULT 1,
  is_premium TINYINT(1) DEFAULT 0,
  default_status ENUM('enabled', 'disabled') DEFAULT 'enabled',
  config JSON DEFAULT NULL,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Mapping of parents to the modules they are entitled to
CREATE TABLE IF NOT EXISTS parent_module_subscriptions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  parent_id INT NOT NULL,
  module_id INT NOT NULL,
  subscription_plan_id INT DEFAULT NULL,
  granted_by INT DEFAULT NULL,
  grant_source ENUM('plan', 'manual', 'system', 'trial') DEFAULT 'plan',
  start_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  status ENUM('active', 'scheduled', 'expired', 'revoked') DEFAULT 'active',
  notes VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_parent_module (parent_id, module_id),
  CONSTRAINT fk_parent_module_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_parent_module_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  CONSTRAINT fk_parent_module_plan FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL,
  CONSTRAINT fk_parent_module_granted_by FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Seed the critical "Position Lock" module so it can be assigned right away
INSERT INTO modules (code, name, description, is_toggleable, is_premium, default_status)
VALUES ('position_lock', 'Position Lock', 'Locks student device position updates unless enabled for the parent subscription', 1, 1, 'disabled')
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description);