FeatureFlow AI

-- Enable UUID extension for unique IDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT, -- For email/password, or NULL for OAuth
    provider TEXT, -- 'email', 'google', 'github', etc.
    provider_id TEXT, -- ID from OAuth provider
    name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE users IS 'Stores user accounts and profiles.';

-- Organizations Table (for teams)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    stripe_customer_id TEXT, -- Stripe Customer ID for billing
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE organizations IS 'Represents a team or company.';
CREATE INDEX idx_organizations_owner_id ON organizations(owner_id);

-- Organization Members Table (many-to-many relationship)
CREATE TABLE organization_members (
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role TEXT NOT NULL DEFAULT 'member', -- 'owner', 'admin', 'member'
    PRIMARY KEY (organization_id, user_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE organization_members IS 'Links users to organizations with specific roles.';

-- Integrations Table (for feature flag & analytics providers)
CREATE TABLE integrations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    type TEXT NOT NULL, -- 'feature_flag', 'analytics'
    provider TEXT NOT NULL, -- 'launchdarkly', 'posthog', 'mixpanel', 'google_analytics'
    api_key TEXT NOT NULL, -- Encrypted API key or OAuth token
    config JSONB, -- Additional configuration (e.g., project IDs, webhook secrets)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (organization_id, type, provider)
);
COMMENT ON TABLE integrations IS 'Stores API keys and configuration for third-party services.';
CREATE INDEX idx_integrations_organization_id ON integrations(organization_id);

-- Experiments Table
CREATE TABLE experiments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    feature_flag_provider_id UUID NOT NULL REFERENCES integrations(id) ON DELETE RESTRICT, -- Which FF integration it uses
    analytics_provider_id UUID NOT NULL REFERENCES integrations(id) ON DELETE RESTRICT, -- Which Analytics integration it uses
    feature_flag_key TEXT NOT NULL, -- The key for the feature flag in the FF provider
    start_date TIMESTAMP WITH TIME ZONE,
    end_date TIMESTAMP WITH TIME ZONE,
    status TEXT NOT NULL DEFAULT 'draft', -- 'draft', 'running', 'completed', 'archived'
    control_variant TEXT NOT NULL,
    variant_details JSONB, -- e.g., {'a': 'description', 'b': 'description'}
    target_metrics JSONB, -- e.g., [{'name': 'conversion_rate', 'threshold': 0.05, 'direction': 'increase'}]
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE experiments IS 'Details about each A/B experiment being tracked.';
CREATE INDEX idx_experiments_organization_id ON experiments(organization_id);

-- Experiment Results Table (stores summarized AI-generated insights)
CREATE TABLE experiment_results (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    experiment_id UUID NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
    analysis_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    summary_insight TEXT, -- AI-generated summary of findings
    detailed_report JSONB, -- Full AI-generated report (e.g., variant performance, statistical significance, segment insights)
    raw_data_snapshot JSONB, -- Optional: Snapshot of raw data used for analysis (e.g., aggregated metrics)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE experiment_results IS 'Stores AI-generated analysis and insights for experiments.';
CREATE INDEX idx_experiment_results_experiment_id ON experiment_results(experiment_id);

-- Webhooks Table (for receiving data from FF/Analytics providers)
CREATE TABLE webhooks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    integration_id UUID REFERENCES integrations(id) ON DELETE SET NULL, -- Link to integration if applicable
    endpoint_url TEXT NOT NULL, -- The URL where the webhook expects to receive data
    secret TEXT, -- Secret for verifying webhook payloads
    events JSONB, -- List of events this webhook is configured for
    status TEXT NOT NULL DEFAULT 'active', -- 'active', 'inactive'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE webhooks IS 'Manages incoming webhooks from external services.';
CREATE INDEX idx_webhooks_organization_id ON webhooks(organization_id);