-- Create project subscriptions table for Vigilant and VAZO Link signups
CREATE TABLE IF NOT EXISTS project_subscriptions (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    project_name VARCHAR(50) NOT NULL CHECK (project_name IN ('vigilant', 'vazo-link')),
    subscription_type VARCHAR(20) NOT NULL CHECK (subscription_type IN ('updates', 'waitlist')),
    subscriber_type VARCHAR(50) DEFAULT 'community_member',
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'unsubscribed', 'bounced')),
    confirmation_token VARCHAR(100) UNIQUE,
    confirmed_at TIMESTAMP,
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unsubscribed_at TIMESTAMP,
    unsubscribe_reason TEXT,
    emails_sent INTEGER DEFAULT 0,
    last_email_sent TIMESTAMP,
    ip_address INET,
    user_agent TEXT,
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_project_subscriptions_email ON project_subscriptions(email);
CREATE INDEX IF NOT EXISTS idx_project_subscriptions_project ON project_subscriptions(project_name);
CREATE INDEX IF NOT EXISTS idx_project_subscriptions_status ON project_subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_project_subscriptions_token ON project_subscriptions(confirmation_token);

-- Create function to generate reference numbers
CREATE OR REPLACE FUNCTION generate_subscription_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'SUB-' || TO_CHAR(CURRENT_DATE, 'YYYY') || '-' || 
                           LPAD(nextval('project_subscriptions_id_seq')::text, 4, '0');
    NEW.confirmation_token := encode(gen_random_bytes(32), 'hex');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for reference number generation
DROP TRIGGER IF EXISTS trigger_generate_subscription_reference ON project_subscriptions;
CREATE TRIGGER trigger_generate_subscription_reference
    BEFORE INSERT ON project_subscriptions
    FOR EACH ROW
    EXECUTE FUNCTION generate_subscription_reference();

-- Create view for subscription analytics
CREATE OR REPLACE VIEW subscription_analytics AS
SELECT 
    project_name,
    subscription_type,
    COUNT(*) as total_subscriptions,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_subscriptions,
    COUNT(CASE WHEN status = 'unsubscribed' THEN 1 END) as unsubscribed,
    COUNT(CASE WHEN confirmed_at IS NOT NULL THEN 1 END) as confirmed_subscriptions,
    DATE_TRUNC('month', subscribed_at) as month
FROM project_subscriptions
GROUP BY project_name, subscription_type, DATE_TRUNC('month', subscribed_at)
ORDER BY month DESC, project_name;
