-- compose/init/04_diary_schema.sql
-- Schema extensions for Crumbforest diary functionality

-- Children table
CREATE TABLE IF NOT EXISTS children (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  age           INT NULL,
  parent_email  VARCHAR(255) NULL,
  token         VARCHAR(255) UNIQUE NOT NULL COMMENT 'Access token for QR code',
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_token (token),
  INDEX idx_parent_email (parent_email)
) ENGINE=InnoDB COMMENT='Children/users who own diary entries';

-- Diary entries table
CREATE TABLE IF NOT EXISTS diary_entries (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  child_id      INT NOT NULL,
  entry_text    TEXT NOT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  INDEX idx_child_id (child_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB COMMENT='Diary entries written by children';

-- Audit log table for GDPR compliance
CREATE TABLE IF NOT EXISTS audit_log (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  action        VARCHAR(100) NOT NULL COMMENT 'Action type (e.g., diary_indexed, diary_searched)',
  entity_type   VARCHAR(50) NOT NULL COMMENT 'Entity type (e.g., diary_entry, child)',
  entity_id     INT NOT NULL COMMENT 'ID of the entity affected',
  user_id       INT NULL COMMENT 'User who performed the action (NULL for system)',
  metadata      JSON NULL COMMENT 'Additional metadata as JSON',
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_action (action),
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_user (user_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB COMMENT='Immutable audit log for all system actions (GDPR compliance)';

-- Extend post_vectors table for diary support
-- Add columns if they don't exist
SET @db_name = DATABASE();
SET @table_name = 'post_vectors';

-- Check and add post_type column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = @table_name
  AND COLUMN_NAME = 'post_type'
);

SET @sql = IF(@col_exists = 0,
  CONCAT('ALTER TABLE ', @table_name, ' ADD COLUMN post_type ENUM(\'post\', \'diary\', \'document\') NOT NULL DEFAULT \'post\' AFTER post_id'),
  'SELECT "Column post_type already exists" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update existing enum to include 'document' if column exists
SET @sql_update = IF(@col_exists > 0,
  CONCAT('ALTER TABLE ', @table_name, ' MODIFY post_type ENUM(\'post\', \'diary\', \'document\') NOT NULL DEFAULT \'post\''),
  'SELECT "Column does not exist yet" AS message'
);

PREPARE stmt2 FROM @sql_update;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- Check and add child_id column
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = @table_name
  AND COLUMN_NAME = 'child_id'
);

SET @sql = IF(@col_exists = 0,
  CONCAT('ALTER TABLE ', @table_name, ' ADD COLUMN child_id INT NULL AFTER post_type, ADD INDEX idx_child_id (child_id)'),
  'SELECT "Column child_id already exists" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Remove foreign key constraint from post_vectors.post_id
-- This allows diary entries to be tracked without being in posts table
SET @fk_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = @table_name
  AND CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND CONSTRAINT_NAME LIKE '%post_vectors_ibfk%'
);

SET @fk_name = (
  SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = @table_name
  AND CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND CONSTRAINT_NAME LIKE '%post_vectors_ibfk%'
  LIMIT 1
);

SET @sql = IF(@fk_exists > 0,
  CONCAT('ALTER TABLE ', @table_name, ' DROP FOREIGN KEY ', @fk_name),
  'SELECT "No foreign key constraint to drop" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;