#!/usr/bin/env tsx
import { supabase } from './client.js';
async function createTables(): Promise<void> {
console.log('🔧 Creating WebForge database tables...');
const serviceClient = supabase.getServiceClient();
const tables = [
{
name: 'design_styles',
sql: `
CREATE TABLE IF NOT EXISTS design_styles (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
industry TEXT NOT NULL,
style_dna JSONB NOT NULL,
dos TEXT[] NOT NULL DEFAULT '{}',
donts TEXT[] NOT NULL DEFAULT '{}',
tokens TEXT NOT NULL,
raw_length INTEGER NOT NULL DEFAULT 0,
businesses TEXT[] NOT NULL DEFAULT '{}',
category TEXT NOT NULL,
category_label TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_design_styles_category ON design_styles(category);
CREATE INDEX IF NOT EXISTS idx_design_styles_industry ON design_styles(industry);
CREATE INDEX IF NOT EXISTS idx_design_styles_businesses ON design_styles USING GIN(businesses);
ALTER TABLE design_styles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow read access to design_styles" ON design_styles;
CREATE POLICY "Allow read access to design_styles" ON design_styles
FOR SELECT USING (true);
`
},
{
name: 'design_palettes',
sql: `
CREATE TABLE IF NOT EXISTS design_palettes (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
mood TEXT[] NOT NULL DEFAULT '{}',
industries TEXT[] NOT NULL DEFAULT '{}',
category TEXT NOT NULL,
primary_light TEXT NOT NULL,
primary_dark TEXT NOT NULL,
accent_light TEXT NOT NULL,
heading_font TEXT NOT NULL,
body_font TEXT NOT NULL,
border_radius TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_design_palettes_category ON design_palettes(category);
CREATE INDEX IF NOT EXISTS idx_design_palettes_mood ON design_palettes USING GIN(mood);
CREATE INDEX IF NOT EXISTS idx_design_palettes_industries ON design_palettes USING GIN(industries);
ALTER TABLE design_palettes ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow read access to design_palettes" ON design_palettes;
CREATE POLICY "Allow read access to design_palettes" ON design_palettes
FOR SELECT USING (true);
`
},
{
name: 'style_palette_compatibility',
sql: `
CREATE TABLE IF NOT EXISTS style_palette_compatibility (
style_id TEXT NOT NULL,
palette_id TEXT NOT NULL,
score INTEGER NOT NULL CHECK (score >= 1 AND score <= 5),
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::TEXT, NOW()) NOT NULL,
PRIMARY KEY (style_id, palette_id)
);
CREATE INDEX IF NOT EXISTS idx_compatibility_style_score ON style_palette_compatibility(style_id, score DESC);
CREATE INDEX IF NOT EXISTS idx_compatibility_palette_score ON style_palette_compatibility(palette_id, score DESC);
CREATE INDEX IF NOT EXISTS idx_compatibility_score ON style_palette_compatibility(score DESC);
ALTER TABLE style_palette_compatibility ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow read access to style_palette_compatibility" ON style_palette_compatibility;
CREATE POLICY "Allow read access to style_palette_compatibility" ON style_palette_compatibility
FOR SELECT USING (true);
`
}
];
// Create each table
for (const table of tables) {
try {
console.log(`📝 Creating table: ${table.name}`);
// Execute each SQL statement separately to handle potential errors
const statements = table.sql.split(';').filter(stmt => stmt.trim());
for (const statement of statements) {
if (statement.trim()) {
const { error } = await serviceClient.rpc('exec_sql', {
sql: statement.trim() + ';'
});
if (error) {
console.error(`❌ Error executing SQL for ${table.name}:`, error);
// Continue with next statement instead of failing completely
}
}
}
console.log(`✅ Table ${table.name} processed`);
} catch (error) {
console.error(`❌ Failed to create table ${table.name}:`, error);
}
}
// Create trigger function if not exists
try {
console.log('📝 Creating trigger function...');
const { error } = await serviceClient.rpc('exec_sql', {
sql: `
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = TIMEZONE('utc'::TEXT, NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_design_styles_updated_at ON design_styles;
CREATE TRIGGER update_design_styles_updated_at
BEFORE UPDATE ON design_styles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
DROP TRIGGER IF EXISTS update_design_palettes_updated_at ON design_palettes;
CREATE TRIGGER update_design_palettes_updated_at
BEFORE UPDATE ON design_palettes
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
`
});
if (error) {
console.error(`⚠️ Warning: Could not create triggers:`, error);
} else {
console.log(`✅ Triggers created`);
}
} catch (error) {
console.error(`⚠️ Warning: Could not create triggers:`, error);
}
console.log('🎉 Table creation completed!');
}
// Test connection and create tables
async function main(): Promise<void> {
try {
const isConnected = await supabase.testConnection();
if (!isConnected) {
throw new Error('Failed to connect to Supabase');
}
await createTables();
} catch (error) {
console.error('💥 Failed:', error);
process.exit(1);
}
}
if (import.meta.url === `file://${process.argv[1]}`) {
main();
}
