-- Mutabaah Ceria Database Schema
-- Version 1.0.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------

-- Table: admin_users
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` varchar(100) NOT NULL,
  `firebase_uid` varchar(128) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `role` varchar(20) NOT NULL DEFAULT 'user',
  `assigned_class_ids` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: school_classes
CREATE TABLE IF NOT EXISTS `school_classes` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `level` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: students
CREATE TABLE IF NOT EXISTS `students` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `nis` varchar(50) DEFAULT NULL,
  `code` varchar(20) NOT NULL,
  `class_id` varchar(100) NOT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `points` int(11) NOT NULL DEFAULT 0,
  `last_active` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `class_id` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: activities
CREATE TABLE IF NOT EXISTS `activities` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `points` int(11) NOT NULL DEFAULT 0,
  `category` varchar(50) DEFAULT NULL,
  `type` enum('checkbox','count','duration') NOT NULL DEFAULT 'checkbox',
  `icon` varchar(50) DEFAULT NULL,
  `order_weight` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: prohibited_activities
CREATE TABLE IF NOT EXISTS `prohibited_activities` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `penalty_points` int(11) NOT NULL DEFAULT 0,
  `category` varchar(50) DEFAULT NULL,
  `severity` enum('low','medium','high') NOT NULL DEFAULT 'low',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: daily_logs
CREATE TABLE IF NOT EXISTS `daily_logs` (
  `id` varchar(100) NOT NULL,
  `student_id` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `activity_responses` longtext DEFAULT NULL,
  `prohibited_responses` longtext DEFAULT NULL,
  `total_points` int(11) NOT NULL DEFAULT 0,
  `is_validated` tinyint(1) NOT NULL DEFAULT 0,
  `validated_at` timestamp NULL DEFAULT NULL,
  `validated_by` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `student_date` (`student_id`,`date`),
  KEY `student_id` (`student_id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: settings
CREATE TABLE IF NOT EXISTS `settings` (
  `key` varchar(100) NOT NULL,
  `value` longtext DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: rewards
CREATE TABLE IF NOT EXISTS `rewards` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `cost` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `stock` int(11) DEFAULT -1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: reward_redemptions
CREATE TABLE IF NOT EXISTS `reward_redemptions` (
  `id` varchar(100) NOT NULL,
  `student_id` varchar(100) NOT NULL,
  `reward_id` varchar(100) NOT NULL,
  `points_at_redemption` int(11) NOT NULL,
  `status` enum('pending','approved','rejected','delivered') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `reward_id` (`reward_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: projects
CREATE TABLE IF NOT EXISTS `projects` (
  `id` varchar(100) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `target_points` int(11) NOT NULL DEFAULT 0,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: point_transactions
CREATE TABLE IF NOT EXISTS `point_transactions` (
  `id` varchar(100) NOT NULL,
  `student_id` varchar(100) NOT NULL,
  `amount` int(11) NOT NULL,
  `type` enum('addition','deduction','redemption','correction') NOT NULL,
  `reference_id` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Constraints for table `students`
ALTER TABLE `students`
  ADD CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `school_classes` (`id`) ON DELETE CASCADE;

-- Constraints for table `daily_logs`
ALTER TABLE `daily_logs`
  ADD CONSTRAINT `daily_logs_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

-- Constraints for table `reward_redemptions`
ALTER TABLE `reward_redemptions`
  ADD CONSTRAINT `reward_redemptions_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `reward_redemptions_ibfk_2` FOREIGN KEY (`reward_id`) REFERENCES `rewards` (`id`) ON DELETE CASCADE;