'use strict';

const ORG_SCOPED_TABLES = [
  'audit_logs',
  'companies',
  'contacts',
  'contracts',
  'csat_surveys',
  'deal_stages',
  'deals',
  'deliverables',
  'email_templates',
  'imports',
  'invoice_line_items',
  'invoices',
  'leave_requests',
  'leads',
  'milestones',
  'notifications',
  'payments',
  'pipelines',
  'products',
  'project_risks',
  'project_status_reports',
  'project_templates',
  'projects',
  'public_holidays',
  'resource_allocations',
  'service_lines',
  'services',
  'skills',
  'support_slas',
  'support_tickets',
  'tags',
  'task_dependencies',
  'tasks',
  'team_member_skills',
  'team_members',
  'tenants',
  'ticket_comments',
  'time_entries',
  'timesheets',
  'users',
];

const RESERVED_SUBDOMAINS = new Set([
  'www',
  'api',
  'admin',
  'app',
  'dashboard',
  'localhost',
]);

function normalizeSubdomain(value) {
  if (typeof value !== 'string') {
    return '';
  }

  return value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9-]+/g, '-')
    .replace(/-{2,}/g, '-')
    .replace(/^-+|-+$/g, '');
}

function isReservedSubdomain(value) {
  return RESERVED_SUBDOMAINS.has(value) || value.startsWith('127-');
}

function getSafeSubdomain(tenantId, rawValue) {
  const normalized = normalizeSubdomain(rawValue);

  if (normalized && !isReservedSubdomain(normalized)) {
    return normalized;
  }

  return `tenant-${String(tenantId).split('-')[0]}`;
}

async function normalizeTenantSubdomains(queryInterface, Sequelize, transaction) {
  const tenants = await queryInterface.sequelize.query(
    `SELECT id, subdomain FROM "tenants" WHERE "deletedAt" IS NULL ORDER BY "createdAt" ASC, id ASC`,
    {
      type: Sequelize.QueryTypes.SELECT,
      transaction,
    },
  );

  const seen = new Set();

  for (const tenant of tenants) {
    const baseSubdomain = getSafeSubdomain(tenant.id, tenant.subdomain);
    let candidate = baseSubdomain;
    let suffix = 1;

    while (seen.has(candidate)) {
      candidate = `${baseSubdomain}-${suffix}`;
      suffix += 1;
    }

    seen.add(candidate);

    if (candidate !== tenant.subdomain) {
      await queryInterface.sequelize.query(
        `UPDATE "tenants" SET "subdomain" = :subdomain, "updatedAt" = NOW() WHERE id = :id`,
        {
          replacements: {
            id: tenant.id,
            subdomain: candidate,
          },
          transaction,
        },
      );
    }
  }
}

function buildScopedPolicySql(tableName) {
  return `
    CREATE POLICY "${tableName}_organization_isolation"
    ON "${tableName}"
    FOR ALL
    USING (
      COALESCE(current_setting('app.global_access', true), 'false') = 'true'
      OR "organizationsId" = NULLIF(current_setting('app.current_org', true), '')::uuid
    )
    WITH CHECK (
      COALESCE(current_setting('app.global_access', true), 'false') = 'true'
      OR "organizationsId" = NULLIF(current_setting('app.current_org', true), '')::uuid
    )
  `;
}

function buildOrganizationPolicySql() {
  return `
    CREATE POLICY "organizations_self_or_global"
    ON "organizations"
    FOR ALL
    USING (
      COALESCE(current_setting('app.global_access', true), 'false') = 'true'
      OR "id" = NULLIF(current_setting('app.current_org', true), '')::uuid
    )
    WITH CHECK (
      COALESCE(current_setting('app.global_access', true), 'false') = 'true'
      OR "id" = NULLIF(current_setting('app.current_org', true), '')::uuid
    )
  `;
}

module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      await normalizeTenantSubdomains(queryInterface, Sequelize, transaction);

      await queryInterface.sequelize.query(
        'DROP INDEX IF EXISTS "tenants_subdomain_unique_active"',
        { transaction },
      );
      await queryInterface.sequelize.query(
        'CREATE UNIQUE INDEX "tenants_subdomain_unique_active" ON "tenants" ("subdomain") WHERE "deletedAt" IS NULL',
        { transaction },
      );

      for (const tableName of ORG_SCOPED_TABLES) {
        await queryInterface.sequelize.query(
          `ALTER TABLE "${tableName}" ENABLE ROW LEVEL SECURITY`,
          { transaction },
        );
        await queryInterface.sequelize.query(
          `ALTER TABLE "${tableName}" FORCE ROW LEVEL SECURITY`,
          { transaction },
        );
        await queryInterface.sequelize.query(
          `DROP POLICY IF EXISTS "${tableName}_organization_isolation" ON "${tableName}"`,
          { transaction },
        );
        await queryInterface.sequelize.query(buildScopedPolicySql(tableName), {
          transaction,
        });
      }

      await queryInterface.sequelize.query(
        'ALTER TABLE "organizations" ENABLE ROW LEVEL SECURITY',
        { transaction },
      );
      await queryInterface.sequelize.query(
        'ALTER TABLE "organizations" FORCE ROW LEVEL SECURITY',
        { transaction },
      );
      await queryInterface.sequelize.query(
        'DROP POLICY IF EXISTS "organizations_self_or_global" ON "organizations"',
        { transaction },
      );
      await queryInterface.sequelize.query(buildOrganizationPolicySql(), {
        transaction,
      });

      await queryInterface.sequelize.query(
        `
          UPDATE "users"
          SET "app_roleId" = super_admin.id,
              "updatedAt" = NOW()
          FROM (
            SELECT id
            FROM "roles"
            WHERE name = 'Super Administrator'
            LIMIT 1
          ) AS super_admin
          WHERE "users"."email" = 'admin@flatlogic.com'
        `,
        { transaction },
      );

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },

  async down(queryInterface) {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      await queryInterface.sequelize.query(
        'DROP INDEX IF EXISTS "tenants_subdomain_unique_active"',
        { transaction },
      );

      for (const tableName of ORG_SCOPED_TABLES) {
        await queryInterface.sequelize.query(
          `DROP POLICY IF EXISTS "${tableName}_organization_isolation" ON "${tableName}"`,
          { transaction },
        );
        await queryInterface.sequelize.query(
          `ALTER TABLE "${tableName}" DISABLE ROW LEVEL SECURITY`,
          { transaction },
        );
        await queryInterface.sequelize.query(
          `ALTER TABLE "${tableName}" NO FORCE ROW LEVEL SECURITY`,
          { transaction },
        );
      }

      await queryInterface.sequelize.query(
        'DROP POLICY IF EXISTS "organizations_self_or_global" ON "organizations"',
        { transaction },
      );
      await queryInterface.sequelize.query(
        'ALTER TABLE "organizations" DISABLE ROW LEVEL SECURITY',
        { transaction },
      );
      await queryInterface.sequelize.query(
        'ALTER TABLE "organizations" NO FORCE ROW LEVEL SECURITY',
        { transaction },
      );
      await queryInterface.sequelize.query(
        `
          UPDATE "users"
          SET "app_roleId" = administrator.id,
              "updatedAt" = NOW()
          FROM (
            SELECT id
            FROM "roles"
            WHERE name = 'Administrator'
            LIMIT 1
          ) AS administrator
          WHERE "users"."email" = 'admin@flatlogic.com'
        `,
        { transaction },
      );

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },
};
