ProjectPal: Dev Context Hub

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  display_name TEXT,
  avatar_url TEXT,
  supabase_id UUID UNIQUE NOT NULL, -- Link to Supabase auth.users.id
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
COMMENT ON TABLE users IS 'Application users, linked to Supabase auth.';

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL, -- For friendly URLs
  owner_id UUID REFERENCES users(id) ON DELETE SET NULL,
  stripe_customer_id TEXT, -- Stripe customer ID for billing
  subscription_status TEXT, -- e.g., 'active', 'inactive', 'trialing'
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
COMMENT ON TABLE organizations IS 'Teams or companies using ProjectPal.';

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_projects_org_id ON projects(org_id);
COMMENT ON TABLE projects IS 'Individual projects managed within an organization.';

CREATE TYPE project_role AS ENUM ('viewer', 'editor', 'admin');

CREATE TABLE project_members (
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  role project_role DEFAULT 'viewer' NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (project_id, user_id)
);
CREATE INDEX idx_project_members_user_id ON project_members(user_id);
COMMENT ON TABLE project_members IS 'Defines which users have access to which projects and their roles.';

CREATE TABLE commands (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE NOT NULL,
  alias TEXT NOT NULL, -- e.g., "dev", "test", "build"
  command_string TEXT NOT NULL, -- e.g., "npm run dev", "docker-compose up"
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (project_id, alias) -- Ensure unique aliases per project
);
CREATE INDEX idx_commands_project_id ON commands(project_id);
COMMENT ON TABLE commands IS 'Project-specific commands/scripts.';

CREATE TABLE environment_variables (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE NOT NULL,
  key TEXT NOT NULL,
  value TEXT, -- Can be a placeholder like "{{STRIPE_SECRET_KEY}}" or actual value
  description TEXT,
  is_secret BOOLEAN DEFAULT FALSE, -- Placeholder for UI, not actual secret management
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (project_id, key)
);
CREATE INDEX idx_env_vars_project_id ON environment_variables(project_id);
COMMENT ON TABLE environment_variables IS 'Project-specific environment variable templates or values.';

CREATE TABLE project_links (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  url TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_project_links_project_id ON project_links(project_id);
COMMENT ON TABLE project_links IS 'Important links related to the project (docs, deployments, etc.).';

CREATE TABLE cli_tokens (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  token_hash TEXT UNIQUE NOT NULL, -- Hashed token, never store plain text
  name TEXT, -- e.g., "My Laptop", "CI Server"
  last_used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_cli_tokens_user_id ON cli_tokens(user_id);
COMMENT ON TABLE cli_tokens IS 'API tokens for CLI access, stored as hashes.';