CREATE DATABASE IF NOT EXISTS comic_canon_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE comic_canon_app;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS character_canons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    character_name VARCHAR(100) NOT NULL,
    species VARCHAR(100) DEFAULT '',
    age VARCHAR(50) DEFAULT '',
    personality TEXT,
    backstory TEXT,
    strengths TEXT,
    weaknesses TEXT,
    goals TEXT,
    fears TEXT,
    favorite_sayings TEXT,
    appearance_notes TEXT,
    default_expression VARCHAR(50) DEFAULT 'happy',
    default_body_pose VARCHAR(50) DEFAULT 'front',
    default_head_pose VARCHAR(50) DEFAULT 'front',
    arm_pose VARCHAR(50) DEFAULT 'relaxed',
    leg_pose VARCHAR(50) DEFAULT 'standing',
    image_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS resource_library (
    id INT AUTO_INCREMENT PRIMARY KEY,
    resource_type ENUM('background','object','sound_effect','music') NOT NULL,
    title VARCHAR(100) NOT NULL,
    resource_url TEXT NOT NULL,
    thumbnail_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comic_stories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    story_json LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

INSERT INTO resource_library (resource_type, title, resource_url, thumbnail_url) VALUES
('background', 'Classroom', 'backgrounds/classroom.png', 'backgrounds/classroom.png'),
('background', 'Space', 'backgrounds/space.png', 'backgrounds/space.png'),
('background', 'Forest', 'backgrounds/forest.png', 'backgrounds/forest.png'),
('object', 'Desk', 'objects/desk.png', 'objects/desk.png'),
('object', 'Magic Book', 'objects/magic_book.png', 'objects/magic_book.png'),
('object', 'Rocket', 'objects/rocket.png', 'objects/rocket.png'),
('sound_effect', 'Boom', 'sounds/boom.mp3', ''),
('sound_effect', 'Pop', 'sounds/pop.mp3', ''),
('music', 'Adventure Theme', 'music/adventure.mp3', ''),
('music', 'Mystery Theme', 'music/mystery.mp3', '');
