TierLock

-- For TierLock's own users/admins (using Supabase Auth)
-- CREATE TABLE auth.users ... (managed by Supabase)

-- Customers of the SaaS applications using TierLock
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    external_user_id TEXT UNIQUE NOT NULL, -- The user ID from the client's own auth system (e.g., their own auth.users.id)
    stripe_customer_id TEXT UNIQUE,        -- Stripe Customer ID
    email TEXT UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE customers IS 'Represents the end-users of SaaS applications using TierLock.';
COMMENT ON COLUMN customers.external_user_id IS 'The unique ID of the user from the client''s own authentication system.';

-- Defined features that can be gated
CREATE TABLE features (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT UNIQUE NOT NULL,             -- e.g., 'unlimited_projects', 'ai_integration'
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_features_name ON features (name);
COMMENT ON TABLE features IS 'Defines all possible features that can be gated by TierLock.';

-- Subscription Plans (corresponding to Stripe Product/Price IDs)
CREATE TABLE plans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stripe_price_id TEXT UNIQUE NOT NULL,  -- Stripe Price ID (recurring price)
    stripe_product_id TEXT,                -- Stripe Product ID
    name TEXT NOT NULL,                    -- e.g., 'Free', 'Pro', 'Enterprise'
    description TEXT,
    tier_level INT DEFAULT 0,              -- For ordering/hierarchy (e.g., 0=Free, 1=Pro, 2=Enterprise)
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_plans_stripe_price_id ON plans (stripe_price_id);
COMMENT ON TABLE plans IS 'Defines subscription plans, linked to Stripe Price IDs.';

-- Junction table for features and plans (which features are included in which plan)
CREATE TABLE plan_features (
    plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
    feature_id UUID NOT NULL REFERENCES features(id) ON DELETE CASCADE,
    max_value INT,                         -- Optional: for features with limits (e.g., max_projects=10)
    PRIMARY KEY (plan_id, feature_id)
);
COMMENT ON TABLE plan_features IS 'Links features to plans, defining which features each plan includes.';

-- Customer Subscriptions (linking customers to their active Stripe subscriptions)
CREATE TABLE customer_subscriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    plan_id UUID NOT NULL REFERENCES plans(id), -- The plan they are subscribed to
    stripe_subscription_id TEXT UNIQUE NOT NULL, -- Stripe Subscription ID
    status TEXT NOT NULL,                  -- e.g., 'active', 'trialing', 'canceled', 'past_due'
    current_period_start TIMESTAMP WITH TIME ZONE,
    current_period_end TIMESTAMP WITH TIME ZONE,
    trial_start TIMESTAMP WITH TIME ZONE,
    trial_end TIMESTAMP WITH TIME ZONE,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (customer_id, stripe_subscription_id) -- A customer might have multiple subscriptions if a complex use case
);
CREATE INDEX idx_customer_subscriptions_customer_id ON customer_subscriptions (customer_id);
CREATE INDEX idx_customer_subscriptions_stripe_subscription_id ON customer_subscriptions (stripe_subscription_id);
COMMENT ON TABLE customer_subscriptions IS 'Tracks active subscriptions for customers, linked to Stripe.';
COMMENT ON COLUMN customer_subscriptions.status IS 'Status of the subscription from Stripe (e.g., active, trialing, canceled).';

-- Webhook events for auditing/debugging
CREATE TABLE webhook_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id TEXT UNIQUE NOT NULL,         -- Stripe event ID
    event_type TEXT NOT NULL,              -- e.g., 'customer.subscription.updated'
    payload JSONB NOT NULL,
    processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_webhook_events_event_id ON webhook_events (event_id);
COMMENT ON TABLE webhook_events IS 'Stores incoming Stripe webhook events for processing and auditing.';

-- Optional: Custom entitlements/overrides for specific customers
CREATE TABLE custom_entitlements (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    feature_id UUID NOT NULL REFERENCES features(id) ON DELETE CASCADE,
    has_access BOOLEAN NOT NULL DEFAULT TRUE,
    max_value INT,                         -- Override plan''s max_value
    expires_at TIMESTAMP WITH TIME ZONE,   -- Temporary access
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (customer_id, feature_id)
);
COMMENT ON TABLE custom_entitlements IS 'Allows overriding plan-based feature access for individual customers.';