-- Sample SQLite database schema for kysely-codegen example with CHECK constraint enums
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  first_name TEXT,
  last_name TEXT,
  role TEXT NOT NULL CHECK (role IN ('admin', 'moderator', 'user', 'guest')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended', 'banned')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT 1
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT,
  author_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived', 'deleted')),
  priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
  category TEXT CHECK (category IN ('technology', 'lifestyle', 'business', 'education', 'entertainment')),
  published_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  post_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'approved' CHECK (status IN ('pending', 'approved', 'rejected', 'spam')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  color TEXT DEFAULT '#000000'
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Insert some sample data
INSERT INTO users (email, username, first_name, last_name, role, status) VALUES
  ('john@example.com', 'john_doe', 'John', 'Doe', 'admin', 'active'),
  ('jane@example.com', 'jane_smith', 'Jane', 'Smith', 'moderator', 'active'),
  ('bob@example.com', 'bob_wilson', 'Bob', 'Wilson', 'user', 'active');

INSERT INTO tags (name, color) VALUES
  ('JavaScript', '#f7df1e'),
  ('TypeScript', '#3178c6'),
  ('SQLite', '#003b57'),
  ('Tutorial', '#28a745');

INSERT INTO posts (title, content, author_id, status, priority, category) VALUES
  ('Getting Started with Kysely', 'Learn how to use Kysely with TypeScript...', 1, 'published', 'high', 'technology'),
  ('SQLite Best Practices', 'Tips and tricks for working with SQLite...', 2, 'published', 'normal', 'technology'),
  ('Draft Post', 'This is a draft post...', 1, 'draft', 'low', 'education');

INSERT INTO post_tags (post_id, tag_id) VALUES
  (1, 1), (1, 2), (1, 4),
  (2, 3), (2, 4),
  (3, 2);

INSERT INTO comments (post_id, user_id, content, status) VALUES
  (1, 2, 'Great tutorial! Very helpful.', 'approved'),
  (1, 3, 'Thanks for sharing this.', 'approved'),
  (2, 1, 'Excellent tips on SQLite optimization.', 'approved');
