EcoCode Insights

-- Enable PostgreSQL extensions for Supabase if needed (e.g., uuid-ossp for UUIDs)
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT, -- For email/password auth
    github_id BIGINT UNIQUE,
    github_username TEXT,
    github_access_token TEXT, -- Encrypted in production
    stripe_customer_id TEXT UNIQUE,
    current_plan TEXT DEFAULT 'free' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE users IS 'Stores user information, including GitHub integration details and subscription status.';
COMMENT ON COLUMN users.github_access_token IS 'OAuth token for GitHub API access, should be encrypted at rest.';

-- Repositories Table
CREATE TABLE repositories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    github_repo_id BIGINT UNIQUE NOT NULL, -- GitHub's internal repo ID
    repo_name TEXT NOT NULL,                -- e.g., 'owner/repo-name'
    repo_url TEXT NOT NULL,
    is_private BOOLEAN DEFAULT TRUE NOT NULL,
    last_scan_at TIMESTAMP WITH TIME ZONE,
    webhook_id BIGINT, -- GitHub webhook ID for future updates
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (user_id, github_repo_id) -- A user can link a repo only once
);
CREATE INDEX idx_repositories_user_id ON repositories(user_id);
COMMENT ON TABLE repositories IS 'Stores linked GitHub repositories for each user.';

-- ScanResults Table
CREATE TABLE scan_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    repo_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    scan_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    green_score INT NOT NULL, -- e.g., 0-100
    bundle_size_kb INT,
    recommendations JSONB, -- Stores an array of structured recommendations
    raw_analysis_data JSONB, -- Optional: store full analysis output for debugging/future use
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_scan_results_repo_id ON scan_results(repo_id);
CREATE INDEX idx_scan_results_timestamp ON scan_results(scan_timestamp);
COMMENT ON TABLE scan_results IS 'Stores results of each code scan for a repository.';
COMMENT ON COLUMN scan_results.recommendations IS 'JSONB array of recommended optimizations, e.g., [{"type": "bundle", "message": "Reduce lodash size", "details": "..."}, ...]';

-- Subscriptions Table (for Stripe integration)
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    stripe_subscription_id TEXT UNIQUE NOT NULL,
    stripe_price_id TEXT NOT NULL,
    status TEXT NOT NULL, -- e.g., 'active', 'canceled', 'past_due'
    current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
    current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    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);
COMMENT ON TABLE subscriptions IS 'Manages user subscriptions synchronized with Stripe.';