-- Zapier Clone Database Schema
CREATE DATABASE IF NOT EXISTS quantu41_zap;
USE quantu41_zap;

-- Workflows table
CREATE TABLE workflows (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_template BOOLEAN DEFAULT FALSE,
    shared_with TEXT, -- JSON array of user IDs
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Workflow steps (triggers and actions)
CREATE TABLE workflow_steps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    workflow_id INT NOT NULL,
    step_type ENUM('trigger', 'action', 'condition') NOT NULL,
    step_order INT NOT NULL,
    service_type VARCHAR(100) NOT NULL, -- email, webhook, database, file, api
    configuration JSON NOT NULL,
    conditions JSON, -- For conditional logic
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
);

-- Workflow executions
CREATE TABLE workflow_executions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    workflow_id INT NOT NULL,
    status ENUM('pending', 'running', 'completed', 'failed', 'retrying') DEFAULT 'pending',
    trigger_data JSON,
    execution_log TEXT,
    error_message TEXT,
    retry_count INT DEFAULT 0,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
);

-- Scheduled workflows
CREATE TABLE scheduled_workflows (
    id INT PRIMARY KEY AUTO_INCREMENT,
    workflow_id INT NOT NULL,
    cron_expression VARCHAR(100) NOT NULL,
    next_run TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
);

INSERT INTO workflows (user_id, name, description, is_template) VALUES 
(1, 'Email Notification Template', 'Send email when webhook is triggered', TRUE),
(1, 'Database Sync Template', 'Sync data between databases', TRUE);