CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(120) NOT NULL,
  role ENUM('parent', 'admin') NOT NULL DEFAULT 'parent',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE levels (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  level_number INT UNSIGNED NOT NULL UNIQUE,
  title VARCHAR(120) NOT NULL,
  required_exp INT UNSIGNED NOT NULL,
  points_bonus INT NOT NULL DEFAULT 0,
  external_reward VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_levels_required_exp (required_exp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE children (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  avatar VARCHAR(80) NULL,
  current_points INT NOT NULL DEFAULT 0,
  current_exp INT NOT NULL DEFAULT 0,
  current_level INT UNSIGNED NOT NULL DEFAULT 1,
  child_pin VARCHAR(255) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_children_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_children_parent (parent_id),
  INDEX idx_children_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id INT UNSIGNED NOT NULL,
  parent_id INT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(32) NULL,
  category ENUM('Haushalt', 'Schule', 'Verhalten', 'Selbststaendigkeit', 'Gesundheit', 'Freizeit', 'Sonstiges') NOT NULL DEFAULT 'Sonstiges',
  points_reward INT NOT NULL DEFAULT 0,
  exp_reward INT NOT NULL DEFAULT 0,
  external_reward VARCHAR(255) NULL,
  due_date DATE NULL,
  due_time TIME NULL,
  repeat_type ENUM('none', 'daily', 'weekly', 'monthly') NOT NULL DEFAULT 'none',
  requires_parent_approval TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('open', 'submitted', 'waiting_parent_review', 'confirmed', 'needs_improvement', 'reward_received') NOT NULL DEFAULT 'open',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tasks_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_tasks_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tasks_child_status (child_id, status),
  INDEX idx_tasks_parent_status (parent_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_submissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_id INT UNSIGNED NOT NULL,
  child_id INT UNSIGNED NOT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  child_comment TEXT NULL,
  proof_image VARCHAR(255) NULL,
  status ENUM('waiting_parent_review', 'confirmed', 'needs_improvement') NOT NULL DEFAULT 'waiting_parent_review',
  parent_id INT UNSIGNED NOT NULL,
  parent_comment TEXT NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_submissions_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_submissions_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_submissions_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_task_submissions_parent_status (parent_id, status),
  INDEX idx_task_submissions_child_status (child_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id INT UNSIGNED NOT NULL,
  parent_id INT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(32) NULL,
  category VARCHAR(80) NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  status ENUM('active', 'paused', 'completed', 'archived') NOT NULL DEFAULT 'active',
  points_reward INT NOT NULL DEFAULT 0,
  exp_reward INT NOT NULL DEFAULT 0,
  external_reward VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_projects_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_projects_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_projects_child_status (child_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_milestones (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id INT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  target_value INT UNSIGNED NOT NULL DEFAULT 1,
  current_value INT UNSIGNED NOT NULL DEFAULT 0,
  points_reward INT NOT NULL DEFAULT 0,
  exp_reward INT NOT NULL DEFAULT 0,
  external_reward VARCHAR(255) NULL,
  status ENUM('open', 'waiting_parent_review', 'achieved', 'needs_improvement') NOT NULL DEFAULT 'open',
  achieved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_project_milestones_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_project_milestones_project_status (project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE shop_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NOT NULL,
  child_id INT UNSIGNED NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(32) NULL,
  category VARCHAR(80) NULL,
  points_cost INT UNSIGNED NOT NULL,
  max_purchases INT UNSIGNED NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_shop_items_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_shop_items_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  INDEX idx_shop_items_parent_active (parent_id, active),
  INDEX idx_shop_items_child_active (child_id, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE shop_purchases (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_item_id INT UNSIGNED NOT NULL,
  child_id INT UNSIGNED NOT NULL,
  parent_id INT UNSIGNED NOT NULL,
  points_cost INT UNSIGNED NOT NULL,
  status ENUM('waiting_parent_review', 'approved', 'declined', 'cancelled') NOT NULL DEFAULT 'waiting_parent_review',
  child_message TEXT NULL,
  parent_comment TEXT NULL,
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_shop_purchases_item FOREIGN KEY (shop_item_id) REFERENCES shop_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_shop_purchases_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_shop_purchases_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_shop_purchases_parent_status (parent_id, status),
  INDEX idx_shop_purchases_child_status (child_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE point_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id INT UNSIGNED NOT NULL,
  amount INT NOT NULL,
  type ENUM('mission_reward', 'project_reward', 'shop_purchase', 'manual_adjustment', 'level_bonus') NOT NULL,
  source_type VARCHAR(60) NOT NULL,
  source_id INT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_point_transactions_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  INDEX idx_point_transactions_child_created (child_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exp_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id INT UNSIGNED NOT NULL,
  amount INT NOT NULL,
  type ENUM('mission_reward', 'project_reward', 'manual_adjustment') NOT NULL,
  source_type VARCHAR(60) NOT NULL,
  source_id INT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_exp_transactions_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  INDEX idx_exp_transactions_child_created (child_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE badges (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(32) NULL,
  rule_type VARCHAR(80) NULL,
  rule_value INT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE child_badges (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  child_id INT UNSIGNED NOT NULL,
  badge_id INT UNSIGNED NOT NULL,
  awarded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  source_type VARCHAR(60) NULL,
  source_id INT UNSIGNED NULL,
  CONSTRAINT fk_child_badges_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  CONSTRAINT fk_child_badges_badge FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE,
  UNIQUE KEY uq_child_badge (child_id, badge_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  child_id INT UNSIGNED NULL,
  type VARCHAR(80) NOT NULL,
  title VARCHAR(160) NOT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notifications_child FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  INDEX idx_notifications_user_read (user_id, is_read),
  INDEX idx_notifications_child_read (child_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  setting_key VARCHAR(120) NOT NULL,
  setting_value TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_settings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_user_setting (user_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO levels (level_number, title, required_exp, points_bonus, external_reward, active) VALUES
  (1, 'Anfaenger-Held', 0, 0, NULL, 1),
  (2, 'Zimmer-Ritter', 100, 10, NULL, 1),
  (3, 'Schul-Abenteurer', 250, 20, NULL, 1),
  (4, 'Familien-Helfer', 450, 30, NULL, 1),
  (5, 'Superheld des Alltags', 700, 50, NULL, 1)
ON DUPLICATE KEY UPDATE
  title = VALUES(title),
  required_exp = VALUES(required_exp),
  points_bonus = VALUES(points_bonus),
  external_reward = VALUES(external_reward),
  active = VALUES(active);

INSERT INTO badges (title, description, icon, rule_type, rule_value, active) VALUES
  ('Mutiger Starter', 'Die erste Mission wurde geschafft.', '⭐', 'missions_confirmed', 1, 1),
  ('Aufraeum-Profi', 'Viele Aufraeum-Missionen geschafft.', '🧹', 'category_confirmed', 5, 1),
  ('Hausaufgaben-Held', 'Schul-Missionen mit Ausdauer gemeistert.', '📚', 'category_confirmed', 5, 1),
  ('Wochenheld', 'Eine starke Woche voller Fortschritt.', '🏅', 'streak_days', 7, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);
