import { pool } from "./db";

export async function createApiKeysTable() {
  try {
    // Create API keys table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS api_keys (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        key_hash TEXT NOT NULL UNIQUE,
        key_preview TEXT NOT NULL,
        is_active BOOLEAN DEFAULT true,
        created_by INTEGER NOT NULL REFERENCES users(id),
        last_used TIMESTAMP,
        expires_at TIMESTAMP,
        permissions TEXT[] DEFAULT ARRAY['read:categories', 'read:accounts'],
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW()
      )
    `);
    console.log("API Keys table created successfully");
    
    // Add FUP field to clients table if it doesn't exist
    const fupColumnExists = await pool.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name='clients' AND column_name='fup'
    `);
    
    if (fupColumnExists.rows.length === 0) {
      await pool.query(`
        ALTER TABLE clients ADD COLUMN fup TEXT
      `);
      console.log("FUP field added to clients table successfully");
    }
    
  } catch (error) {
    console.error('Error creating API keys table or updating clients table:', error);
    throw error;
  }
}