-- Users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  userid VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  bio TEXT,
  profile VARCHAR(500),
  deviceid VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AI Tools reference
CREATE TABLE IF NOT EXISTS ai_tools (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  icon VARCHAR(50),
  color VARCHAR(20),
  url VARCHAR(500) NOT NULL
);

-- Insert default AI tools
INSERT INTO ai_tools (name, icon, color, url) VALUES
  ('ChatGPT', 'chatgpt', '#10A37F', 'https://chat.openai.com'),
  ('Gemini', 'gemini', '#4285F4', 'https://gemini.google.com'),
  ('Claude', 'claude', '#D97706', 'https://claude.ai'),
  ('Midjourney', 'midjourney', '#000000', 'https://www.midjourney.com'),
  ('DALL-E', 'dalle', '#10A37F', 'https://labs.openai.com'),
  ('Stable Diffusion', 'sd', '#A855F7', 'https://stability.ai'),
  ('Copilot', 'copilot', '#0078D4', 'https://copilot.microsoft.com'),
  ('Perplexity', 'perplexity', '#20808D', 'https://www.perplexity.ai'),
  ('Leonardo AI', 'leonardo', '#8B5CF6', 'https://leonardo.ai'),
  ('Runway', 'runway', '#000000', 'https://runwayml.com');

-- Posts table
CREATE TABLE IF NOT EXISTS posts (
  id SERIAL PRIMARY KEY,
  userid VARCHAR(20) NOT NULL REFERENCES users(userid),
  prompt_text TEXT NOT NULL,
  caption TEXT,
  ai_tool_id INTEGER REFERENCES ai_tools(id),
  post_type VARCHAR(20) DEFAULT 'image',
  love_count INTEGER DEFAULT 0,
  fire_count INTEGER DEFAULT 0,
  wow_count INTEGER DEFAULT 0,
  comment_count INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Post media
CREATE TABLE IF NOT EXISTS post_media (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  media_url VARCHAR(500) NOT NULL,
  media_type VARCHAR(20) DEFAULT 'image',
  thumbnail_url VARCHAR(500),
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reactions
CREATE TABLE IF NOT EXISTS post_reactions (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  userid VARCHAR(20) NOT NULL REFERENCES users(userid),
  reaction_type VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(post_id, userid)
);

-- Bookmarks
CREATE TABLE IF NOT EXISTS post_bookmarks (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  userid VARCHAR(20) NOT NULL REFERENCES users(userid),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(post_id, userid)
);

-- Comments
CREATE TABLE IF NOT EXISTS comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  userid VARCHAR(20) NOT NULL REFERENCES users(userid),
  comment_text TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger: reaction counts
CREATE OR REPLACE FUNCTION update_reaction_counts() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.reaction_type = 'love' THEN
      UPDATE posts SET love_count = love_count + 1 WHERE id = NEW.post_id;
    ELSIF NEW.reaction_type = 'fire' THEN
      UPDATE posts SET fire_count = fire_count + 1 WHERE id = NEW.post_id;
    ELSIF NEW.reaction_type = 'wow' THEN
      UPDATE posts SET wow_count = wow_count + 1 WHERE id = NEW.post_id;
    END IF;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    IF OLD.reaction_type = 'love' THEN
      UPDATE posts SET love_count = GREATEST(love_count - 1, 0) WHERE id = OLD.post_id;
    ELSIF OLD.reaction_type = 'fire' THEN
      UPDATE posts SET fire_count = GREATEST(fire_count - 1, 0) WHERE id = OLD.post_id;
    ELSIF OLD.reaction_type = 'wow' THEN
      UPDATE posts SET wow_count = GREATEST(wow_count - 1, 0) WHERE id = OLD.post_id;
    END IF;
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.reaction_type = 'love' THEN
      UPDATE posts SET love_count = GREATEST(love_count - 1, 0) WHERE id = OLD.post_id;
    ELSIF OLD.reaction_type = 'fire' THEN
      UPDATE posts SET fire_count = GREATEST(fire_count - 1, 0) WHERE id = OLD.post_id;
    ELSIF OLD.reaction_type = 'wow' THEN
      UPDATE posts SET wow_count = GREATEST(wow_count - 1, 0) WHERE id = OLD.post_id;
    END IF;
    IF NEW.reaction_type = 'love' THEN
      UPDATE posts SET love_count = love_count + 1 WHERE id = NEW.post_id;
    ELSIF NEW.reaction_type = 'fire' THEN
      UPDATE posts SET fire_count = fire_count + 1 WHERE id = NEW.post_id;
    ELSIF NEW.reaction_type = 'wow' THEN
      UPDATE posts SET wow_count = wow_count + 1 WHERE id = NEW.post_id;
    END IF;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reaction_count_trigger
AFTER INSERT OR DELETE OR UPDATE ON post_reactions
FOR EACH ROW EXECUTE FUNCTION update_reaction_counts();

-- Trigger: comment count
CREATE OR REPLACE FUNCTION update_comment_count() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = GREATEST(comment_count - 1, 0) WHERE id = OLD.post_id;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER comment_count_trigger
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

-- Indexes
CREATE INDEX idx_posts_userid ON posts(userid);
CREATE INDEX idx_posts_created ON posts(created_at DESC);
CREATE INDEX idx_posts_ai_tool ON posts(ai_tool_id);
CREATE INDEX idx_post_media_post ON post_media(post_id);
CREATE INDEX idx_reactions_post ON post_reactions(post_id);
CREATE INDEX idx_reactions_user ON post_reactions(userid);
CREATE INDEX idx_bookmarks_user ON post_bookmarks(userid);
CREATE INDEX idx_comments_post ON comments(post_id);
