Skill Scout

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    stripe_customer_id TEXT, -- For Stripe integration
    subscription_status TEXT -- e.g., 'active', 'inactive', 'trialing'
);

CREATE TABLE profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY, -- Link to Supabase auth.users
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL, -- Redundant but useful for quick lookups
    role TEXT DEFAULT 'employee' NOT NULL, -- 'employee', 'manager', 'admin'
    avatar_url TEXT,
    bio TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Skills table (master list of skills)
CREATE TABLE skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- User-specific skills (many-to-many with proficiency)
CREATE TABLE user_skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
    skill_id UUID REFERENCES skills(id) ON DELETE CASCADE,
    proficiency INTEGER NOT NULL CHECK (proficiency >= 1 AND proficiency <= 5), -- 1=beginner, 5=expert
    is_primary BOOLEAN DEFAULT FALSE, -- Highlight key skills
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (user_id, skill_id) -- A user can only have a specific skill once
);

-- Projects table
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'open' NOT NULL, -- 'open', 'in_progress', 'completed', 'archived'
    start_date DATE,
    end_date DATE,
    created_by UUID REFERENCES profiles(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Project skill requirements (many-to-many linking projects and skills)
CREATE TABLE project_skill_requirements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    skill_id UUID REFERENCES skills(id) ON DELETE CASCADE,
    min_proficiency INTEGER NOT NULL CHECK (min_proficiency >= 1 AND min_proficiency <= 5),
    is_essential BOOLEAN DEFAULT TRUE, -- Is this skill mandatory for the project?
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (project_id, skill_id)
);

-- Add indexes for performance
CREATE INDEX idx_profiles_organization_id ON profiles (organization_id);
CREATE INDEX idx_user_skills_user_id ON user_skills (user_id);
CREATE INDEX idx_user_skills_skill_id ON user_skills (skill_id);
CREATE INDEX idx_projects_organization_id ON projects (organization_id);
CREATE INDEX idx_project_skill_requirements_project_id ON project_skill_requirements (project_id);
CREATE INDEX idx_project_skill_requirements_skill_id ON project_skill_requirements (skill_id);

-- Enable Row Level Security (RLS) for all tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_skill_requirements ENABLE ROW LEVEL SECURITY;