SaaS/database/schema.sql

-- SaaS Automation Platform Database Schema

-- Create n8n database
CREATE DATABASE n8n;

-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    is_verified BOOLEAN DEFAULT false,
    verification_token VARCHAR(255),
    reset_password_token VARCHAR(255),
    reset_password_expires TIMESTAMP,
    role VARCHAR(50) DEFAULT 'free',
    subscription_plan VARCHAR(50) DEFAULT 'free',
    subscription_status VARCHAR(50) DEFAULT 'active',
    subscription_starts_at TIMESTAMP,
    subscription_ends_at TIMESTAMP,
    workflow_limit INTEGER DEFAULT 3,
    user_limit INTEGER DEFAULT 1,
    features JSONB DEFAULT '{"telegram_notifications": false, "email_notifications": true, "api_access": false, "premium_templates": false}',
    telegram_chat_id VARCHAR(255),
    notification_preferences JSONB DEFAULT '{"email": true, "telegram": false, "workflow_status": true, "system_updates": false}',
    onboarding_completed BOOLEAN DEFAULT false,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Workflows table
CREATE TABLE workflows (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    n8n_workflow_id VARCHAR(255),
    status VARCHAR(50) DEFAULT 'inactive',
    trigger_type VARCHAR(100),
    trigger_config JSONB,
    actions_config JSONB,
    is_active BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for n8n_workflow_id
CREATE INDEX idx_workflows_n8n_workflow_id ON workflows(n8n_workflow_id);

-- Templates table (automation templates)
CREATE TABLE templates (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    trigger_type VARCHAR(100),
    trigger_config JSONB,
    actions_config JSONB,
    n8n_template JSONB,
    tags TEXT[],
    is_featured BOOLEAN DEFAULT false,
    install_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User installed templates
CREATE TABLE user_templates (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    template_id INTEGER REFERENCES templates(id) ON DELETE CASCADE,
    workflow_id INTEGER REFERENCES workflows(id) ON DELETE CASCADE,
    installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, template_id)
);

-- Integrations table (OAuth connections)
CREATE TABLE integrations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    provider VARCHAR(100) NOT NULL,
    provider_user_id VARCHAR(255),
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP,
    scopes TEXT[],
    account_name VARCHAR(255),
    account_email VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Webhooks table
CREATE TABLE webhooks (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    trigger_type VARCHAR(100),
    secret_key VARCHAR(255),
    url_path VARCHAR(255) UNIQUE,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Webhook logs
CREATE TABLE webhook_logs (
    id SERIAL PRIMARY KEY,
    webhook_id INTEGER REFERENCES webhooks(id) ON DELETE CASCADE,
    request_method VARCHAR(10),
    request_headers JSONB,
    request_payload JSONB,
    response_status INTEGER,
    response_payload JSONB,
    processing_time_ms INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Workflow executions/logs
CREATE TABLE workflow_executions (
    id SERIAL PRIMARY KEY,
    workflow_id INTEGER REFERENCES workflows(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    n8n_execution_id VARCHAR(255),
    status VARCHAR(50),
    trigger_data JSONB,
    execution_data JSONB,
    error_message TEXT,
    started_at TIMESTAMP,
    finished_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Chat history (AI chatbot)
CREATE TABLE chat_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    session_id VARCHAR(255),
    message_type VARCHAR(20) CHECK (message_type IN ('user', 'assistant')),
    content TEXT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_workflows_user_id ON workflows(user_id);
CREATE INDEX idx_workflows_status ON workflows(status);
CREATE INDEX idx_templates_category ON templates(category);
CREATE INDEX idx_integrations_user_id ON integrations(user_id);
CREATE INDEX idx_integrations_provider ON integrations(provider);
CREATE INDEX idx_webhooks_user_id ON webhooks(user_id);
CREATE INDEX idx_webhooks_url_path ON webhooks(url_path);
CREATE INDEX idx_webhook_logs_webhook_id ON webhook_logs(webhook_id);
CREATE INDEX idx_workflow_executions_workflow_id ON workflow_executions(workflow_id);
CREATE INDEX idx_workflow_executions_user_id ON workflow_executions(user_id);
CREATE INDEX idx_chat_history_user_id ON chat_history(user_id);
CREATE INDEX idx_chat_history_session_id ON chat_history(session_id);

-- Subscription plans table
CREATE TABLE subscription_plans (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    workflow_limit INTEGER NOT NULL,
    user_limit INTEGER NOT NULL,
    features JSONB NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User subscriptions history
CREATE TABLE subscription_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    plan_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'GBP',
    payment_method VARCHAR(50),
    payment_status VARCHAR(50) DEFAULT 'pending',
    payment_provider_id VARCHAR(255),
    billing_period_start TIMESTAMP NOT NULL,
    billing_period_end TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User teams table (for multi-user accounts)
CREATE TABLE user_teams (
    id SERIAL PRIMARY KEY,
    owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    member_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(50) DEFAULT 'member',
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    joined_at TIMESTAMP,
    status VARCHAR(50) DEFAULT 'pending',
    UNIQUE(owner_id, member_id)
);

-- Notifications table
CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) NOT NULL,
    data JSONB,
    is_read BOOLEAN DEFAULT false,
    sent_via VARCHAR(50),
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- API tokens table
CREATE TABLE api_tokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    permissions JSONB DEFAULT '["workflows:read"]',
    last_used_at TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audit logs table
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(100),
    details JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System settings table
CREATE TABLE system_settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(100) UNIQUE NOT NULL,
    value JSONB NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT false,
    updated_by INTEGER REFERENCES users(id),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample data
INSERT INTO subscription_plans (name, price_monthly, price_yearly, workflow_limit, user_limit, features) VALUES
('Free', 0.00, 0.00, 3, 1, '{"telegram_notifications": false, "email_notifications": true, "api_access": false, "premium_templates": false, "support": "community", "execution_limit": 100}'),
('Pro', 150.00, 1500.00, 10, 2, '{"telegram_notifications": true, "email_notifications": true, "api_access": true, "premium_templates": true, "support": "email", "execution_limit": 10000}'),
('Enterprise', 0.00, 0.00, -1, -1, '{"telegram_notifications": true, "email_notifications": true, "api_access": true, "premium_templates": true, "support": "priority", "execution_limit": -1, "custom_integrations": true}');

INSERT INTO system_settings (key, value, description, is_public) VALUES
('trial_duration_days', '7', 'Trial period duration in days', false),
('max_workflows_free', '3', 'Maximum workflows for free users', false),
('maintenance_mode', 'false', 'System maintenance mode', true),
('welcome_message', '"Welcome to AutomationHub! Start automating your workflows today."', 'Welcome message for new users', true);

INSERT INTO templates (name, description, category, trigger_type, trigger_config, actions_config, tags, is_featured) VALUES
('Instagram to CRM', 'Automatically sync new Instagram followers to your CRM', 'Social Media', 'instagram_follower',
 '{"event": "new_follower"}',
 '{"actions": [{"type": "crm_create_contact", "fields": ["username", "profile_url"]}]}',
 ARRAY['instagram', 'crm', 'automation'], true),

('Email to Task Manager', 'Create tasks from important emails', 'Productivity', 'email_received',
 '{"filters": {"importance": "high"}}',
 '{"actions": [{"type": "task_create", "fields": ["subject", "sender", "body"]}]}',
 ARRAY['email', 'productivity', 'tasks'], true),

('Form Submission to Slack', 'Post form submissions to Slack channel', 'Communication', 'webhook',
 '{"event": "form_submit"}',
 '{"actions": [{"type": "slack_message", "channel": "#leads"}]}',
 ARRAY['webhook', 'slack', 'forms'], false);

-- Insert global admin (password is 'admin123')
INSERT INTO users (email, password_hash, first_name, last_name, is_verified, role, subscription_plan, workflow_limit, user_limit, features) VALUES
('info@ai-impress.com', '$2b$10$8K1p/a0dclxKqnvzBL5TM.N7EH1Cx0m1K2oOKy2Tc2z1H5U2N3V6K', 'Global', 'Admin', true, 'admin', 'enterprise', -1, -1, '{"telegram_notifications": true, "email_notifications": true, "api_access": true, "premium_templates": true, "support": "priority", "execution_limit": -1, "admin_panel": true, "system_settings": true}');

-- Insert sample user (password is 'password123')
INSERT INTO users (email, password_hash, first_name, last_name, is_verified, subscription_starts_at, subscription_ends_at) VALUES
('demo@example.com', '$2b$10$8K1p/a0dclxKqnvzBL5TM.N7EH1Cx0m1K2oOKy2Tc2z1H5U2N3V6K', 'Demo', 'User', true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '7 days');