This document outlines the database schema for the IdeaScore application. The schema will evolve through the project phases, with the initial focus being on the core functionality.

CREATE TABLE IF NOT EXISTS ideas_v1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  description TEXT,
  effort_score INTEGER NOT NULL CHECK(effort_score BETWEEN 1 AND 10),
  payoff_score INTEGER NOT NULL CHECK(payoff_score BETWEEN 1 AND 10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user_email TEXT NOT NULL,
  is_deleted BOOLEAN DEFAULT 0
);

-- Index for faster querying by user
CREATE INDEX IF NOT EXISTS idx_ideas_user_email ON ideas_v1(user_email);

CREATE TABLE IF NOT EXISTS tags_v1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  color TEXT DEFAULT '#808080',
  user_email TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_deleted BOOLEAN DEFAULT 0,
  UNIQUE(name, user_email)
);

-- Index for faster querying by user
CREATE INDEX IF NOT EXISTS idx_tags_user_email ON tags_v1(user_email);

CREATE TABLE IF NOT EXISTS idea_tags_v1 (
  idea_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  user_email TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (idea_id, tag_id),
  FOREIGN KEY (idea_id) REFERENCES ideas_v1(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags_v1(id) ON DELETE CASCADE
);

-- Indexes for faster joins
CREATE INDEX IF NOT EXISTS idx_idea_tags_idea_id ON idea_tags_v1(idea_id);
CREATE INDEX IF NOT EXISTS idx_idea_tags_tag_id ON idea_tags_v1(tag_id);
CREATE INDEX IF NOT EXISTS idx_idea_tags_user_email ON idea_tags_v1(user_email);

-- Alter the ideas table to add status
ALTER TABLE ideas_v1 ADD COLUMN status TEXT DEFAULT 'new' CHECK(status IN ('new', 'in_progress', 'completed', 'abandoned'));

-- Add index for status-based queries
CREATE INDEX IF NOT EXISTS idx_ideas_status ON ideas_v1(status);

CREATE TABLE IF NOT EXISTS completion_data_v1 (
  idea_id INTEGER PRIMARY KEY,
  actual_effort_score INTEGER CHECK(actual_effort_score BETWEEN 1 AND 10),
  actual_payoff_score INTEGER CHECK(actual_payoff_score BETWEEN 1 AND 10),
  completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completion_notes TEXT,
  FOREIGN KEY (idea_id) REFERENCES ideas_v1(id) ON DELETE CASCADE
);

As the schema evolves, we'll use the following approach:

1. New tables will be created with version suffixes (e.g., _v1, _v2)
2. Data migrations will be performed when upgrading between versions
3. For simple additions, we'll use ALTER TABLE statements
4. For complex changes, we'll create new tables and migrate data
5. We'll maintain backward compatibility for older versions during transitions

• Always use parameterized queries to prevent SQL injection
• Include schema version in database requests
• Include appropriate indexes for frequently queried fields
• Validate all input data before insertion
• Implement soft deletes (is_deleted flag) where appropriate
• Always check for constraints before performing operations

This schema document will be updated as the project progresses through its development phases.