46 lines
1.7 KiB
SQL
46 lines
1.7 KiB
SQL
-- 001_initial_schema.sql
|
|
-- This script creates the initial database schema for Screen Test.
|
|
|
|
-- Create sessions table to store the core user inputs.
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
target_audience TEXT NOT NULL,
|
|
business_idea TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Create personas table to store the generated AI personas for each session.
|
|
CREATE TABLE IF NOT EXISTS personas (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
age INT,
|
|
occupation VARCHAR(255),
|
|
traits TEXT,
|
|
concerns TEXT,
|
|
style TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Create chat_messages table to store the conversation history.
|
|
CREATE TABLE IF NOT EXISTS chat_messages (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_id INT NOT NULL,
|
|
persona_id INT NOT NULL,
|
|
sender ENUM('user', 'persona', 'system') NOT NULL,
|
|
message TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (persona_id) REFERENCES personas(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Create analyses table to store the generated conversation analysis.
|
|
CREATE TABLE IF NOT EXISTS analyses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_id INT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|