-- Personas (miembros familia)
CREATE TABLE IF NOT EXISTS people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  photo_url VARCHAR(255) NULL,
  color_hex CHAR(7) DEFAULT '#888888',
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Periodos de gamificación
CREATE TABLE IF NOT EXISTS periods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  granularity ENUM('daily','weekly','monthly','custom') NOT NULL DEFAULT 'monthly',
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  locked TINYINT(1) DEFAULT 0
);

-- Tareas (definición)
CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  points INT NOT NULL,
  is_general TINYINT(1) DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Asignaciones de tareas a personas
CREATE TABLE IF NOT EXISTS task_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT NOT NULL,
  person_id INT NOT NULL,
  UNIQUE KEY uk_task_person (task_id, person_id),
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE
);

-- Realizaciones de tareas
CREATE TABLE IF NOT EXISTS task_completions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT NOT NULL,
  person_id INT NOT NULL,
  period_id INT NOT NULL,
  completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_general_once (task_id, period_id),
  INDEX idx_person_period (person_id, period_id),
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE,
  FOREIGN KEY (period_id) REFERENCES periods(id) ON DELETE CASCADE
);

-- Puntajes por periodo
CREATE TABLE IF NOT EXISTS period_points (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_id INT NOT NULL,
  person_id INT NOT NULL,
  points INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_period_person (period_id, person_id),
  FOREIGN KEY (period_id) REFERENCES periods(id) ON DELETE CASCADE,
  FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE
);

-- Menús (dos imágenes superponibles por día)
CREATE TABLE IF NOT EXISTS menus (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date DATE NOT NULL,
  image_top_url VARCHAR(255) NULL,
  image_bottom_url VARCHAR(255) NULL,
  note VARCHAR(255) NULL,
  UNIQUE KEY uk_date (date)
);
