/**
 * Sprint 1 & 2 Multi-Tenant Data Architecture & Licensing Migration
 * Creates tenants and tenant_licenses tables, injects tenant_id into domain tables,
 * populates default tenant data, and implements PostgreSQL Row Level Security (RLS).
 */

const DEFAULT_TENANT_ID = '5b39bcb5-506a-456d-b7cb-91730b397cc1';

const DOMAIN_TABLES = [
  'users',
  'departments',
  'classes',
  'subjects',
  'courses',
  'enrollments',
  'course_enrolments',
  'attendance',
  'fee_groups',
  'student_fees',
  'payments',
  'messages',
  'events',
  'expenses',
  'grades',
  'course_grades',
  'assignments',
  'submissions',
  'settings',
  'system_settings',
  'audit_logs',
  'staff_roles',
  'salary_grades',
  'staff_records',
  'leave_types',
  'leave_requests',
  'staff_attendance',
  'payroll_runs',
  'payslips',
  'item_categories',
  'suppliers',
  'store_locations',
  'items',
  'item_stock',
  'stock_transactions',
  'item_issues',
  'exam_groups',
  'question_banks',
  'exam_schedules',
  'exam_attempts',
  'exam_answers',
  'exam_review_audit',
  'notifications',
  'hostels',
  'room_types',
  'rooms',
  'room_assignments',
  'hostel_fees',
  'vehicles',
  'routes',
  'pickup_points',
  'vehicle_routes',
  'transport_allocations',
  'admission_enquiries',
  'visitor_logs',
  'phone_call_logs',
  'postal_dispatch',
  'complaints',
  'clubs',
  'club_memberships',
  'club_announcements',
  'club_attendance',
  'club_chats',
  'hostel_attendance',
  'transport_attendance',
  'crossword_games'
];

exports.up = async function(knex) {
  const isPg = knex.client.config.client === 'pg';

  // 1. Create tenants table
  const hasTenants = await knex.schema.hasTable('tenants');
  if (!hasTenants) {
    await knex.schema.createTable('tenants', (table) => {
      const idColumn = table.uuid('id').primary();
      if (isPg) {
        idColumn.defaultTo(knex.raw('gen_random_uuid()'));
      }
      table.string('name', 255).notNullable();
      table.string('code', 64).unique().notNullable();
      table.string('domain', 255);
      table.string('contact_email', 255);
      table.string('phone', 64);
      table.text('address');
      table.string('status', 32).defaultTo('active'); // active, suspended, trial, grace_period
      table.string('logo_url', 512);
      table.timestamp('created_at').defaultTo(knex.fn.now());
      table.timestamp('updated_at').defaultTo(knex.fn.now());
    });
    console.log('[migration] Created table: tenants');
  }

  // 2. Create tenant_licenses table
  const hasLicenses = await knex.schema.hasTable('tenant_licenses');
  if (!hasLicenses) {
    await knex.schema.createTable('tenant_licenses', (table) => {
      table.string('id', 64).primary();
      table.uuid('tenant_id').notNullable().references('id').inTable('tenants').onDelete('CASCADE');
      table.string('license_key', 128).unique().notNullable();
      table.string('term_name', 64).notNullable(); // Term 1, Term 2, Term 3, Annual
      table.string('academic_year', 32).notNullable();
      table.date('start_date').notNullable();
      table.date('end_date').notNullable();
      table.integer('grace_period_days').defaultTo(7);
      table.integer('max_students').defaultTo(1000);
      table.integer('max_staff').defaultTo(200);
      table.string('status', 32).defaultTo('active'); // active, expired, revoked, overridden
      table.text('override_reason');
      table.string('issued_by', 128);
      table.timestamp('created_at').defaultTo(knex.fn.now());
      table.timestamp('updated_at').defaultTo(knex.fn.now());
    });
    console.log('[migration] Created table: tenant_licenses');
  }

  // 3. Insert default tenant if missing
  const defaultTenant = await knex('tenants').where({ id: DEFAULT_TENANT_ID }).first();
  if (!defaultTenant) {
    await knex('tenants').insert({
      id: DEFAULT_TENANT_ID,
      name: 'Default School Tenant',
      code: 'DEFAULT-001',
      contact_email: 'admin@school.ac.zw',
      status: 'active'
    });

    // Issue initial active term license for default tenant
    const now = new Date();
    const nextYear = new Date();
    nextYear.setFullYear(now.getFullYear() + 1);

    await knex('tenant_licenses').insert({
      id: 'lic-default-001',
      tenant_id: DEFAULT_TENANT_ID,
      license_key: 'LIC-DEFAULT-2026-TERM1-VALID',
      term_name: 'Term 1',
      academic_year: '2026',
      start_date: now.toISOString().slice(0, 10),
      end_date: nextYear.toISOString().slice(0, 10),
      grace_period_days: 14,
      max_students: 5000,
      max_staff: 500,
      status: 'active',
      issued_by: 'SuperAdmin System'
    });
    console.log(`[migration] Inserted ${DEFAULT_TENANT_ID} and active term license`);
  }

  // 4. Inject tenant_id into all domain tables. SKIPPED on SQLite
  // because ALTER TABLE ADD COLUMN with REFERENCES + NOT NULL DEFAULT
  // is unsupported. Production uses Postgres (where RLS enforces
  // tenant_id); dev uses SQLite and the licensing middleware has its
  // own dev-mode bypass (no SUPERADMIN_PUBLIC_KEY → bypass).
  for (const tableName of DOMAIN_TABLES) {
    if (!isPg) break; // SQLite: skip the whole tenant_id injection
    const tableExists = await knex.schema.hasTable(tableName);
    if (tableExists) {
      const hasTenantCol = await knex.schema.hasColumn(tableName, 'tenant_id');
      if (!hasTenantCol) {
        await knex.schema.alterTable(tableName, (table) => {
          table.uuid('tenant_id').notNullable().defaultTo(DEFAULT_TENANT_ID).references('id').inTable('tenants').onDelete('CASCADE');
        });
        console.log(`[migration] Added tenant_id column to ${tableName}`);
      }

      // 5. If PostgreSQL, apply Row Level Security (RLS) policies
      if (isPg) {
        try {
          await knex.schema.raw(`ALTER TABLE "${tableName}" ENABLE ROW LEVEL SECURITY;`);
          await knex.schema.raw(`
            DO $$
            BEGIN
              IF NOT EXISTS (
                SELECT 1 FROM pg_policies WHERE tablename = '${tableName}' AND policyname = 'tenant_isolation_policy'
              ) THEN
                CREATE POLICY tenant_isolation_policy ON "${tableName}"
                  FOR ALL
                  USING (
                    tenant_id = current_setting('app.current_tenant', true)
                    OR current_setting('app.is_super_admin', true) = 'true'
                  )
                  WITH CHECK (
                    tenant_id = current_setting('app.current_tenant', true)
                    OR current_setting('app.is_super_admin', true) = 'true'
                  );
              END IF;
            END $$;
          `);
          console.log(`[migration] Applied PostgreSQL RLS policy on ${tableName}`);
        } catch (rlsErr) {
          console.warn(`[migration] RLS notice for ${tableName}:`, rlsErr.message);
        }
      }
    }
  }
};

exports.down = async function(knex) {
  for (const tableName of DOMAIN_TABLES) {
    const tableExists = await knex.schema.hasTable(tableName);
    if (tableExists) {
      const hasTenantCol = await knex.schema.hasColumn(tableName, 'tenant_id');
      if (hasTenantCol) {
        await knex.schema.alterTable(tableName, (table) => {
          table.dropColumn('tenant_id');
        });
      }
    }
  }
  await knex.schema.dropTableIfExists('tenant_licenses');
  await knex.schema.dropTableIfExists('tenants');
};
