-- Drop existing tables with foreign key constraints first
DROP TABLE IF EXISTS webhooks;
DROP TABLE IF EXISTS workflow_schedules;
DROP TABLE IF EXISTS workflow_executions;
DROP TABLE IF EXISTS workflow_versions;
DROP TABLE IF EXISTS workflows;
DROP TABLE IF EXISTS users;

-- Create users table with UUID as primary key
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create workflows table
CREATE TABLE workflows (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    nodes JSONB,
    edges JSONB,
    user_id UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create workflow_versions table
CREATE TABLE workflow_versions (
    id UUID PRIMARY KEY,
    workflow_id UUID REFERENCES workflows(id),
    version INTEGER NOT NULL,
    nodes JSONB,
    edges JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create workflow_executions table
CREATE TABLE workflow_executions (
    id UUID PRIMARY KEY,
    workflow_id UUID REFERENCES workflows(id),
    status VARCHAR(50) NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    logs JSONB,
    results JSONB
);

-- Create workflow_schedules table
CREATE TABLE workflow_schedules (
    id UUID PRIMARY KEY,
    workflow_id UUID REFERENCES workflows(id),
    cron_expression VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create webhooks table
CREATE TABLE webhooks (
    id UUID PRIMARY KEY,
    workflow_id UUID REFERENCES workflows(id),
    node_id VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a default admin user with UUID
-- Password is 'FlowForge123!' (hashed with bcrypt)
INSERT INTO users (id, email, password, created_at, updated_at) 
VALUES (
    '550e8400-e29b-41d4-a716-446655440000', 
    'admin@flowforge.test', 
    '$2b$10$3euPcmQFCiblsZeEu5s7p.9wVdLajnYhAbcjkru4KkUGBIm3WVYjK',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
);