import type { Knex } from 'knex';
import { getDefaultSitemapSettings } from '@/lib/sitemap-utils';

/**
 * Migration: Create Settings Table
 *
 * Creates the settings table with default values
 */

export async function up(knex: Knex): Promise<void> {
  // Create settings table
  await knex.schema.createTable('settings', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.string('key', 255).notNullable().unique();
    table.jsonb('value').notNullable();
    table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now());
    table.timestamp('updated_at', { useTz: true }).defaultTo(knex.fn.now());
  });

  // Create index
  await knex.schema.raw('CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key)');

  // Enable Row Level Security
  await knex.schema.raw('ALTER TABLE settings ENABLE ROW LEVEL SECURITY');

  // Create RLS policies
  // Only authenticated users can read settings (contains sensitive data like SMTP credentials)
  await knex.schema.raw(`
    CREATE POLICY "Authenticated users can view settings"
      ON settings FOR SELECT
      USING ((SELECT auth.uid()) IS NOT NULL)
  `);

  // Only authenticated users can create/update/delete settings
  await knex.schema.raw(`
    CREATE POLICY "Authenticated users can modify settings"
      ON settings FOR INSERT
      WITH CHECK ((SELECT auth.uid()) IS NOT NULL)
  `);

  await knex.schema.raw(`
    CREATE POLICY "Authenticated users can update settings"
      ON settings FOR UPDATE
      USING ((SELECT auth.uid()) IS NOT NULL)
  `);

  await knex.schema.raw(`
    CREATE POLICY "Authenticated users can delete settings"
      ON settings FOR DELETE
      USING ((SELECT auth.uid()) IS NOT NULL)
  `);

  // Insert default settings (sitemap: Ycode-generated by default for new apps)
  await knex('settings').insert([
    { key: 'site_name', value: JSON.stringify('Ycode Site') },
    { key: 'site_description', value: JSON.stringify('Built with Ycode') },
    { key: 'ycode_version', value: JSON.stringify('0.1.0') },
    { key: 'sitemap', value: JSON.stringify(getDefaultSitemapSettings()) },
    { key: 'ycode_badge', value: JSON.stringify(true) },
    { key: 'timezone', value: JSON.stringify('UTC') },
  ]).onConflict('key').ignore();
}

export async function down(knex: Knex): Promise<void> {
  // Drop policies (both legacy and current names)
  await knex.schema.raw('DROP POLICY IF EXISTS "Settings are viewable by everyone" ON settings');
  await knex.schema.raw('DROP POLICY IF EXISTS "Authenticated users can view settings" ON settings');
  await knex.schema.raw('DROP POLICY IF EXISTS "Authenticated users can modify settings" ON settings');
  await knex.schema.raw('DROP POLICY IF EXISTS "Authenticated users can update settings" ON settings');
  await knex.schema.raw('DROP POLICY IF EXISTS "Authenticated users can delete settings" ON settings');

  // Drop table
  await knex.schema.dropTableIfExists('settings');
}
