DevRoutine

-- Users table for authentication and user profiles
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Projects table to categorize playbooks (optional, for shared routines)
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Playbooks table to define routines
CREATE TABLE playbooks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE SET NULL, -- Nullable for general routines
  name VARCHAR(255) NOT NULL,
  description TEXT,
  frequency VARCHAR(50) NOT NULL, -- e.g., 'daily', 'weekly', 'monthly', 'custom'
  is_archived BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_playbooks_user_id ON playbooks(user_id);

-- Playbook steps table for individual actions within a playbook
CREATE TABLE playbook_steps (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  playbook_id UUID REFERENCES playbooks(id) ON DELETE CASCADE,
  order_index INTEGER NOT NULL, -- Order of steps within a playbook
  description TEXT NOT NULL,
  tool_link VARCHAR(2048), -- Optional link to external tool/resource
  expected_duration_minutes INTEGER, -- Optional estimate for step completion
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT unique_playbook_order UNIQUE (playbook_id, order_index)
);
CREATE INDEX idx_playbook_steps_playbook_id ON playbook_steps(playbook_id);

-- Routine executions table to track instances of a playbook being run
CREATE TABLE routine_executions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  playbook_id UUID REFERENCES playbooks(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  scheduled_at TIMESTAMPTZ NOT NULL, -- When the routine was expected to run
  started_at TIMESTAMPTZ, -- When the user actually started it
  completed_at TIMESTAMPTZ, -- When all steps were completed
  status VARCHAR(50) NOT NULL DEFAULT 'pending', -- 'pending', 'in_progress', 'completed', 'skipped'
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_routine_executions_user_id ON routine_executions(user_id);
CREATE INDEX idx_routine_executions_scheduled_at ON routine_executions(scheduled_at);

-- Step completions table to track individual step progress for an execution
CREATE TABLE step_completions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  routine_execution_id UUID REFERENCES routine_executions(id) ON DELETE CASCADE,
  playbook_step_id UUID REFERENCES playbook_steps(id) ON DELETE CASCADE,
  completed_at TIMESTAMPTZ,
  is_completed BOOLEAN DEFAULT FALSE,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT unique_step_completion UNIQUE (routine_execution_id, playbook_step_id)
);
CREATE INDEX idx_step_completions_routine_execution_id ON step_completions(routine_execution_id);

-- Subscriptions table for Stripe integration
CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  stripe_customer_id VARCHAR(255) UNIQUE NOT NULL,
  stripe_subscription_id VARCHAR(255) UNIQUE NOT NULL,
  plan_id VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL, -- e.g., 'active', 'canceled', 'trialing'
  current_period_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);