CodeSpark Embeds

CREATE TABLE IF NOT EXISTS snippets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  title VARCHAR(120) NOT NULL,
  description TEXT,
  code TEXT NOT NULL,
  language VARCHAR(50) NOT NULL, -- e.g., 'javascript', 'html', 'python'
  embed_id VARCHAR(36) UNIQUE NOT NULL DEFAULT gen_random_uuid(), -- Publicly shareable ID
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_snippets_user_id ON snippets(user_id);
CREATE INDEX idx_snippets_embed_id ON snippets(embed_id);

COMMENT ON TABLE snippets IS 'Stores user-created interactive code snippets.';
COMMENT ON COLUMN snippets.user_id IS 'Foreign key to the auth.users table.';
COMMENT ON COLUMN snippets.embed_id IS 'Unique ID for public embedding, separate from primary key.';

CREATE TABLE IF NOT EXISTS subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  stripe_customer_id TEXT UNIQUE NOT NULL,
  stripe_subscription_id TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL, -- e.g., 'active', 'canceled', 'past_due'
  current_period_end TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_stripe_customer_id ON subscriptions(stripe_customer_id);

COMMENT ON TABLE subscriptions IS 'Manages user subscription status via Stripe.';
COMMENT ON COLUMN subscriptions.user_id IS 'Foreign key to the auth.users table.';