import { Knex } from 'knex';

/**
 * Initial database schema migration
 * Creates all tables needed for the application
 */
export async function up(knex: Knex): Promise<void> {
  // Create restaurant_groups table
  await knex.schema.createTable('restaurant_groups', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.string('name').notNullable();
    table.text('description');
    table.string('logo_url');
    table.string('contact_email');
    table.string('contact_phone');
    table.text('address');
    table.string('website');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());
  });

  // Create restaurant_brands table
  await knex.schema.createTable('restaurant_brands', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('group_id').notNullable().references('id').inTable('restaurant_groups').onDelete('CASCADE');
    table.string('name').notNullable();
    table.text('description');
    table.string('logo_url');
    table.string('theme_color');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('group_id');
  });

  // Create restaurant_outlets table
  await knex.schema.createTable('restaurant_outlets', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('brand_id').notNullable().references('id').inTable('restaurant_brands').onDelete('CASCADE');
    table.string('name').notNullable();
    table.string('country').notNullable();
    table.text('address').notNullable();
    table.string('contact_phone');
    table.string('contact_email');
    table.string('manager_name');
    table.string('pos_id').notNullable().unique();
    table.string('currency').notNullable();
    table.boolean('active').defaultTo(true);
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('brand_id');
    table.index('pos_id');
  });

  // Create users table
  await knex.schema.createTable('users', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.string('email').notNullable().unique();
    table.string('password').notNullable();
    table.string('first_name').notNullable();
    table.string('last_name').notNullable();
    table.string('phone');
    table.jsonb('roles').notNullable().defaultTo('["staff"]');
    table.jsonb('permissions').defaultTo('[]');
    table.boolean('active').defaultTo(true);
    table.timestamp('last_login_at');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('email');
    table.index('roles');
  });

  // Create user_restaurant_access table (for controlling access to restaurants)
  await knex.schema.createTable('user_restaurant_access', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.uuid('restaurant_id').notNullable();
    table.enum('restaurant_level', ['group', 'brand', 'outlet']).notNullable();
    table.jsonb('permissions').defaultTo('[]');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('user_id');
    table.index('restaurant_id');

    // Unique constraint to prevent duplicate access entries
    table.unique(['user_id', 'restaurant_id', 'restaurant_level']);
  });

  // Create api_clients table (for client credentials authentication)
  await knex.schema.createTable('api_clients', (table) => {
    table.string('client_id').primary();
    table.string('client_secret').notNullable();
    table.string('client_name').notNullable();
    table.text('client_description');
    table.jsonb('allowed_scopes').defaultTo('[]');
    table.boolean('active').defaultTo(true);
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('client_id');
    table.index('client_name');
  });

  // Create password_reset_tokens table
  await knex.schema.createTable('password_reset_tokens', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.string('token').notNullable().unique();
    table.timestamp('expires_at').notNullable();
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('token');
    table.index('user_id');
  });

  // Create pos_sync_logs table (for tracking data syncs from POS systems)
  await knex.schema.createTable('pos_sync_logs', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('outlet_id').notNullable().references('id').inTable('restaurant_outlets').onDelete('CASCADE');
    table.string('pos_id').notNullable();
    table.string('sync_type').notNullable();
    table.timestamp('sync_started_at').notNullable();
    table.timestamp('sync_completed_at');
    table.integer('records_processed').defaultTo(0);
    table.string('status').notNullable().defaultTo('pending');
    table.text('error_message');
    table.jsonb('metadata').defaultTo('{}');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('outlet_id');
    table.index('pos_id');
    table.index('sync_started_at');
    table.index('status');
  });

  // Create sales_data table (for storing sales data from POS systems)
  await knex.schema.createTable('sales_data', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('outlet_id').notNullable().references('id').inTable('restaurant_outlets').onDelete('CASCADE');
    table.string('pos_id').notNullable();
    table.string('transaction_id').notNullable();
    table.timestamp('transaction_date').notNullable();
    table.decimal('total_amount', 12, 2).notNullable();
    table.decimal('tax_amount', 12, 2).notNullable();
    table.decimal('discount_amount', 12, 2).defaultTo(0);
    table.string('payment_method');
    table.string('cashier_id');
    table.jsonb('metadata').defaultTo('{}');
    table.jsonb('items').defaultTo('[]');
    table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

    // Add indexes
    table.index('outlet_id');
    table.index('pos_id');
    table.index('transaction_id');
    table.index('transaction_date');
    table.index('payment_method');

    // Ensure transaction IDs are unique per outlet
    table.unique(['outlet_id', 'transaction_id']);
  });
}

/**
 * Rollback migration
 * Drops all tables in reverse order
 */
export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('sales_data');
  await knex.schema.dropTableIfExists('pos_sync_logs');
  await knex.schema.dropTableIfExists('password_reset_tokens');
  await knex.schema.dropTableIfExists('api_clients');
  await knex.schema.dropTableIfExists('user_restaurant_access');
  await knex.schema.dropTableIfExists('users');
  await knex.schema.dropTableIfExists('restaurant_outlets');
  await knex.schema.dropTableIfExists('restaurant_brands');
  await knex.schema.dropTableIfExists('restaurant_groups');
}