import { BadRequestException, ForbiddenException, Injectable, NotFoundException } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, In } from 'typeorm';
import { ChecklistTemplate } from '../entities/checklist-template.entity';
import { ChecklistTemplateItem } from '../entities/checklist-template-item.entity';
import { ChecklistTemplateShare } from '../entities/checklist-template-share.entity';
import { ChecklistTemplateFile } from '../entities/checklist-template-file.entity';
import { AuditChecklist, AuditChecklistStatus } from '../entities/audit-checklist.entity';
import { AuditChecklistItem, ChecklistItemStatus } from '../entities/audit-checklist-item.entity';
import { AuditScheduleRow } from '../../audit-schedules/entities/audit-schedule-row.entity';
import { PreviousNcService } from '../../previous-nc/services/previous-nc.service';
import { ChecklistStorageService } from './checklist-storage.service';
import { ChecklistsService } from './checklists.service';
import { ChecklistFileParserService } from './checklist-file-parser.service';

// Same rules as audit-schedules.service.ts (SUPER_ADMIN_IDS, userCanViewAll, userIsMyAuditsViewAll).
// Keep these in sync with that file.
const SUPER_ADMIN_IDS = [1, 8];
const VIEW_ALL_ROLES = ['super-admin', 'scheme', 'coordinator'];
const TEMPLATES_MODULE_SLUG = 'checklist-templates';

@Injectable()
export class ChecklistTemplatesAccessService {
  constructor(
    @InjectDataSource('scheme_dbs') private readonly ds: DataSource,
    private readonly nc: PreviousNcService,
    private readonly storage: ChecklistStorageService,
    private readonly checklists: ChecklistsService,
    private readonly parser: ChecklistFileParserService,
  ) { }

  // ── access helpers ──────────────────────────────────────────────

  /** PREVIOUS rule (kept, no longer used for access): the NC module's view-all, which also
   *  includes marketing and any other role with NC view-all, so auditors could slip through. */
  isAdminViaNc(userId: number): Promise<boolean> {
    return this.nc.userCanViewAll(userId);
  }

  /** "Can see ALL checklists" = super-admin id, OR a view-all role (super-admin / scheme / coordinator,
   *  same list My Audits uses), OR the 'view-all' permission on the checklist-templates module
   *  (via role or direct grant). Everybody else, auditors included, only gets own + shared. */
  async isAdmin(userId: number): Promise<boolean> {
    if (SUPER_ADMIN_IDS.includes(userId)) return true;

    try {
      const roles: { role_name: string }[] = await this.ds.query(
        `SELECT LOWER(r.name) AS role_name
           FROM users u
           INNER JOIN user_roles ur ON ur.user_id = u.id
           INNER JOIN roles r ON r.id = ur.role_id
          WHERE u.id = ?`,
        [userId],
      );
      if (roles.some((r) => VIEW_ALL_ROLES.includes(r.role_name))) return true;
    } catch {
      /* fall through to the permission checks */
    }

    try {
      const viaRole = await this.ds.query(
        `SELECT 1
           FROM users u
           INNER JOIN user_roles ur ON ur.user_id = u.id
           INNER JOIN role_permissions rp ON rp.role_id = ur.role_id
           INNER JOIN permissions p ON p.id = rp.permission_id
           INNER JOIN modules m ON m.id = p.module_id
          WHERE u.id = ? AND p.action = 'view-all' AND m.slug = ?
          LIMIT 1`,
        [userId, TEMPLATES_MODULE_SLUG],
      );
      if (viaRole.length > 0) return true;
    } catch {
      /* ignore */
    }

    try {
      const direct = await this.ds.query(
        `SELECT 1
           FROM user_permissions up
           INNER JOIN permissions p ON p.id = up.permission_id
           INNER JOIN modules m ON m.id = p.module_id
          WHERE up.user_id = ? AND p.action = 'view-all' AND m.slug = ?
          LIMIT 1`,
        [userId, TEMPLATES_MODULE_SLUG],
      );
      if (direct.length > 0) return true;
    } catch {
      /* user_permissions table may not exist */
    }

    return false;
  }

  private async load(id: number): Promise<ChecklistTemplate> {
    const t = await this.ds.getRepository(ChecklistTemplate).findOne({ where: { id } });
    if (!t) throw new NotFoundException('Checklist not found');
    return t;
  }

  private async canSee(t: ChecklistTemplate, userId: number): Promise<boolean> {
    if (await this.isAdmin(userId)) return true;
    if (t.created_by === userId) return true;
    const share = await this.ds
      .getRepository(ChecklistTemplateShare)
      .findOne({ where: { template_id: t.id, user_id: userId } });
    return !!share;
  }

  private async assertSee(id: number, userId: number): Promise<ChecklistTemplate> {
    const t = await this.load(id);
    if (!(await this.canSee(t, userId))) throw new ForbiddenException('This checklist is not shared with you.');
    return t;
  }

  private async assertEdit(id: number, userId: number): Promise<ChecklistTemplate> {
    const t = await this.load(id);
    if (!((await this.isAdmin(userId)) || t.created_by === userId)) {
      throw new ForbiddenException('Only the owner or a coordinator can change this checklist.');
    }
    return t;
  }

  /** Public wrapper so the legacy controller can enforce the same owner / admin rule. */
  async assertCanEdit(id: number, userId: number): Promise<void> {
    await this.assertEdit(id, userId);
  }

  /** Lead auditor or co-auditor of the audit (same assignment rule as audit-schedules). */
  private async isAssignedToAudit(rowId: number, userId: number): Promise<boolean> {
    const lead = await this.ds.query(
      'SELECT 1 FROM audit_schedule_rows WHERE id = ? AND lead_auditor_id = ? LIMIT 1',
      [rowId, userId],
    );
    if (lead.length > 0) return true;
    const co = await this.ds.query(
      'SELECT 1 FROM audit_row_co_auditors WHERE row_id = ? AND auditor_id = ? LIMIT 1',
      [rowId, userId],
    );
    return co.length > 0;
  }

  /** An audit checklist may be opened / changed by: an admin, the auditor who built it, or (for
   *  checklists built before built_by existed) the audit's lead / co-auditor. */
  async assertChecklistAccess(checklistId: number, userId: number): Promise<AuditChecklist> {
    const cl = await this.ds.getRepository(AuditChecklist).findOne({ where: { id: checklistId } });
    if (!cl) throw new NotFoundException('Checklist not found');
    if (await this.isAdmin(userId)) return cl;
    const builtBy = (cl as any).built_by;
    if (builtBy === userId) return cl;
    if ((builtBy === null || builtBy === undefined) && (await this.isAssignedToAudit(cl.audit_schedule_row_id, userId))) {
      return cl;
    }
    throw new ForbiddenException('This checklist belongs to another auditor.');
  }

  async assertItemAccess(itemId: number, userId: number): Promise<void> {
    const item = await this.ds.getRepository(AuditChecklistItem).findOne({ where: { id: itemId } });
    if (!item) throw new NotFoundException('Item not found');
    await this.assertChecklistAccess(item.audit_checklist_id, userId);
  }

  /** Standards a template is linked to (multi-standard list + legacy single column). */
  private templateStandardIds(t: ChecklistTemplate): number[] {
    const list = Array.isArray(t.standard_ids) ? t.standard_ids : [];
    const all = [...list, t.standard_id].filter((x) => x !== null && x !== undefined).map(Number);
    return [...new Set(all)].filter((n) => Number.isInteger(n) && n > 0);
  }

  private async auditScope(rowId: number): Promise<Set<number>> {
    const row = await this.ds.getRepository(AuditScheduleRow).findOne({
      where: { id: rowId },
      relations: ['standards'],
    });
    if (!row) throw new NotFoundException(`Audit ${rowId} not found`);
    return new Set<number>(((row as any).standards || []).map((s: any) => Number(s.id)));
  }

  // ── library (the auditor's own checklists) ──────────────────────

  /** Checklists this user may manage: admins see all, everyone else sees own + shared. */
  async listVisible(userId: number) {
    const admin = await this.isAdmin(userId);
    const qb = this.ds
      .getRepository(ChecklistTemplate)
      .createQueryBuilder('t')
      .leftJoinAndSelect('t.items', 'items', 'items.is_active = 1')
      .orderBy('t.updated_at', 'DESC')
      .addOrderBy('items.sort_order', 'ASC');
    if (!admin) {
      qb.where(
        '(t.created_by = :me OR t.id IN (SELECT s.template_id FROM checklist_template_shares s WHERE s.user_id = :me))',
        { me: userId },
      );
    }
    qb.andWhere('t.archived_at IS NULL');
    const rows = await qb.getMany();
    const ids = rows.map((r) => r.id);
    const files = ids.length
      ? await this.ds.getRepository(ChecklistTemplateFile).find({ where: { template_id: In(ids) } })
      : [];
    const shares = admin && ids.length
      ? await this.ds.getRepository(ChecklistTemplateShare).find({ where: { template_id: In(ids) } })
      : [];

    return {
      is_admin: admin,
      checklists: rows.map((t) => ({
        id: t.id,
        name: t.name,
        created_by: t.created_by,
        can_edit: admin || t.created_by === userId,
        standard_id: t.standard_id,
        standard_ids: this.templateStandardIds(t),
                is_shareable: !!t.is_shareable,
        items: (t.items ?? []).map((i) => ({
          id: i.id,
          item_text: i.item_text,
          category: i.category,
          sort_order: i.sort_order,
          is_active: i.is_active,
        })),
        files: files
          .filter((f) => f.template_id === t.id)
          .map((f) => ({
            id: f.id,
            original_name: f.original_name,
            size_bytes: f.size_bytes,
            mime: f.mime,
            created_at: f.created_at,
          })),
        shared_user_ids: admin ? shares.filter((s) => s.template_id === t.id).map((s) => s.user_id) : [],
      })),
    };
  }

  async rename(userId: number, id: number, name: string) {
    const t = await this.assertEdit(id, userId);
    const clean = (name ?? '').trim();
    if (!clean) throw new BadRequestException('Checklist name is required.');
    t.name = clean.slice(0, 255);
    return this.ds.getRepository(ChecklistTemplate).save(t);
  }

    async setShareable(userId: number, id: number, isShareable: boolean) {
    await this.assertEdit(id, userId);
    await this.ds.getRepository(ChecklistTemplate).update({ id }, { is_shareable: !!isShareable } as any);
    return { id, is_shareable: !!isShareable };
  }

  async setStandards(userId: number, id: number, standardIds: number[]) {
    const t = await this.assertEdit(id, userId);
    const ids = [...new Set((standardIds ?? []).map(Number).filter((n) => Number.isInteger(n) && n > 0))];
    if (ids.length === 0) throw new BadRequestException('Choose at least one standard.');
    t.standard_id = ids[0];
    t.standard_ids = ids;
    await this.ds.getRepository(ChecklistTemplate).save(t);
    return { id, standard_ids: ids };
  }

  async remove(userId: number, id: number) {
    await this.assertEdit(id, userId);
    return this.checklists.deleteTemplate(id); // refuses if already used on an audit
  }

  async addItem(userId: number, templateId: number, text: string, category?: string) {
    await this.assertEdit(templateId, userId);
    const clean = (text ?? '').trim();
    if (!clean) throw new BadRequestException('Item text is required.');
    const repo = this.ds.getRepository(ChecklistTemplateItem);
    const last = await repo
      .createQueryBuilder('i')
      .select('MAX(i.sort_order)', 'm')
      .where('i.template_id = :id', { id: templateId })
      .getRawOne();
    return repo.save(
      repo.create({
        template_id: templateId,
        item_text: clean.slice(0, 500),
        category: category?.trim() || null,
        sort_order: (Number(last?.m) || 0) + 1,
        is_active: true,
      }),
    );
  }

  /** Editing an item that an audit already used keeps the old wording on that audit: the old row is
   *  hidden and a new row with the new wording is created. Returns the item the list should show. */
  async updateItem(
    userId: number,
    itemId: number,
    patch: { item_text?: string; category?: string | null; is_active?: boolean; sort_order?: number },
  ) {
    const repo = this.ds.getRepository(ChecklistTemplateItem);
    const item = await repo.findOne({ where: { id: itemId } });
    if (!item) throw new NotFoundException('Item not found');
    await this.assertEdit(item.template_id, userId);

    const newText = patch.item_text !== undefined ? patch.item_text.trim() : undefined;
    if (newText !== undefined && !newText) throw new BadRequestException('Item text is required.');
    const textChanged = newText !== undefined && newText !== item.item_text;
    const usedOnAudit = textChanged
      ? await this.ds.getRepository(AuditChecklistItem).count({ where: { template_item_id: itemId } })
      : 0;

    if (textChanged && usedOnAudit > 0) {
      item.is_active = false;
      await repo.save(item);
      return repo.save(
        repo.create({
          template_id: item.template_id,
          item_text: newText!.slice(0, 500),
          category: patch.category !== undefined ? patch.category?.trim() || null : item.category,
          sort_order: item.sort_order,
          is_active: true,
        }),
      );
    }

    if (newText !== undefined) item.item_text = newText.slice(0, 500);
    if (patch.category !== undefined) item.category = patch.category?.trim() || null;
    if (patch.is_active !== undefined) item.is_active = !!patch.is_active;
    if (patch.sort_order !== undefined) item.sort_order = Number(patch.sort_order) || 0;
    return repo.save(item);
  }

  /** Removes the item. If an audit already used it, it is hidden so old reports stay intact. */
  async deleteItem(userId: number, itemId: number) {
    const repo = this.ds.getRepository(ChecklistTemplateItem);
    const item = await repo.findOne({ where: { id: itemId } });
    if (!item) throw new NotFoundException('Item not found');
    await this.assertEdit(item.template_id, userId);
    const used = await this.ds.getRepository(AuditChecklistItem).count({ where: { template_item_id: itemId } });
    if (used > 0) {
      item.is_active = false;
      await repo.save(item);
      return { removed: false, hidden: true };
    }
    await repo.remove(item);
    return { removed: true, hidden: false };
  }

  /** Coordinator / super-admin only: replaces the auditors this checklist is shared with. */
  async setShares(adminId: number, templateId: number, userIds: number[]) {
    if (!(await this.isAdmin(adminId))) {
      throw new ForbiddenException('Only a coordinator or super-admin can share checklists.');
    }
    await this.load(templateId);
    const repo = this.ds.getRepository(ChecklistTemplateShare);
    const current = await repo.find({ where: { template_id: templateId } });
    const want = [...new Set((userIds ?? []).map(Number).filter((n) => Number.isInteger(n) && n > 0))];
    const drop = current.filter((s) => !want.includes(s.user_id));
    const add = want.filter((u) => !current.some((s) => s.user_id === u));
    if (drop.length) await repo.remove(drop);
    if (add.length) {
      await repo.save(add.map((u) => repo.create({ template_id: templateId, user_id: u, granted_by: adminId })));
    }
    return { template_id: templateId, shared_user_ids: want };
  }

  /** Upload a Word / Excel / PDF file to a checklist. Word and Excel files also add their items. */
  async uploadFile(userId: number, templateId: number, file: any) {
    await this.assertEdit(templateId, userId);
    if (!file) throw new BadRequestException('Choose a file to upload.');

    let saved: { dbPath: string; size: number };
    try {
      saved = await this.storage.saveTemplateFile(templateId, file);
    } catch (e: any) {
      throw new BadRequestException(e?.message || 'File could not be saved.');
    }

    const fileRepo = this.ds.getRepository(ChecklistTemplateFile);
    const record = await fileRepo.save(
      fileRepo.create({
        template_id: templateId,
        original_name: String(file.originalname).slice(0, 255),
        stored_path: saved.dbPath,
        mime: file.mimetype ?? null,
        size_bytes: saved.size,
        uploaded_by: userId,
      }),
    );

    const parsed = await this.parser.extract(file.buffer, file.originalname);
    const itemRepo = this.ds.getRepository(ChecklistTemplateItem);
    const existing = await itemRepo.find({ where: { template_id: templateId } });
    const known = new Set(existing.filter((i) => i.is_active).map((i) => i.item_text.trim().toLowerCase()));
    let next = existing.reduce((max, i) => Math.max(max, i.sort_order ?? 0), 0);

    const toAdd: ChecklistTemplateItem[] = [];
    let skipped = 0;
    for (const text of parsed.items) {
      const key = text.trim().toLowerCase();
      if (known.has(key)) {
        skipped++;
        continue;
      }
      known.add(key);
      toAdd.push(
        itemRepo.create({
          template_id: templateId,
          item_text: text,
          category: null,
          sort_order: ++next,
          is_active: true,
        }),
      );
    }
    if (toAdd.length) await itemRepo.save(toAdd);

    return {
      file: record,
      format: parsed.format,
      supported: parsed.supported,
      items_added: toAdd.length,
      items_skipped: skipped,
    };
  }

  async openFile(userId: number, fileId: number) {
    const f = await this.ds.getRepository(ChecklistTemplateFile).findOne({ where: { id: fileId } });
    if (!f) throw new NotFoundException('File not found');
    await this.assertSee(f.template_id, userId);
    const opened = await this.storage.openFile(f.stored_path);
    return { ...opened, filename: f.original_name };
  }

  async deleteFile(userId: number, fileId: number) {
    const repo = this.ds.getRepository(ChecklistTemplateFile);
    const f = await repo.findOne({ where: { id: fileId } });
    if (!f) throw new NotFoundException('File not found');
    await this.assertEdit(f.template_id, userId);
    await this.storage.deleteFile(f.stored_path);
    await repo.remove(f);
    return { removed: true };
  }

  // ── building a checklist for an audit ───────────────────────────

  /** Templates this user may offer for THIS audit: linked to one of the audit's standards (scope of work),
   *  and owned by the user, shared with the user, or the user is a coordinator / super-admin. */
  async availableForAudit(rowId: number, userId: number) {
    const scope = await this.auditScope(rowId);
    if (scope.size === 0) return [];

    const admin = await this.isAdmin(userId);
    const sharedIds = new Set(
      (await this.ds.getRepository(ChecklistTemplateShare).find({ where: { user_id: userId } })).map(
        (s) => s.template_id,
      ),
    );
    const all = await this.ds
      .getRepository(ChecklistTemplate)
      .createQueryBuilder('t')
      .leftJoinAndSelect('t.items', 'items', 'items.is_active = 1')
      .leftJoinAndSelect('t.standard', 'standard')
      .where('t.archived_at IS NULL')
      .orderBy('t.name', 'ASC')
      .addOrderBy('items.sort_order', 'ASC')
      .getMany();

    const visible = all.filter((t) => {
      const inScope = this.templateStandardIds(t).some((id) => scope.has(id));
      if (!inScope) return false;
      // Owner decision 2026-11-04: an auditor's OWN checklist appears on the Checklist tab only when its
      // "Reusable" switch is ON (My Library tab). Previously every own checklist was offered.
      const ownReusable = t.created_by === userId && !!t.is_shareable;
      // Owner decision 2026-11-06: "Reusable" is the OWNER's switch to reuse their checklist for another
      // client. It must NOT publish the checklist to every other auditor (that is why Madara's
      // "Reqiured documents" showed on everyone's audits). Other auditors only get it when a coordinator
      // shares it with them explicitly (checklist_template_shares).
      // PREVIOUS rule (kept for reference):
      // return admin || ownReusable || sharedIds.has(t.id) || !!t.is_shareable;
      return admin || ownReusable || sharedIds.has(t.id);
    });

    // Picker labels: is_mine / owner_name (name only, never the user row) so the auditor can tell their own
    // checklists from reusable ones of other auditors.
    const ownerIds = [...new Set(visible.map((t) => t.created_by).filter((x): x is number => !!x && x !== userId))];
    const names = new Map<number, string>();
    if (ownerIds.length) {
      try {
        const rows: { id: number; firstName: string | null; lastName: string | null }[] = await this.ds.query(
          `SELECT id, firstName, lastName FROM users WHERE id IN (${ownerIds.map(() => '?').join(',')})`,
          ownerIds,
        );
        rows.forEach((u) => names.set(Number(u.id), [u.firstName, u.lastName].filter(Boolean).join(' ').trim()));
      } catch {
        /* names are cosmetic */
      }
    }
    return visible.map((t) =>
      Object.assign(t, {
        is_mine: t.created_by === userId,
        owner_name: t.created_by && t.created_by !== userId ? names.get(t.created_by) || null : null,
        shared_with_me: sharedIds.has(t.id),
      }),
    );
  }

  /** Checklists on this audit that this user built. Coordinators and super-admins see all of them. */
  async visibleChecklists(rowId: number, userId: number) {
    const all = await this.checklists.getChecklistsForAuditor(rowId);
    if (await this.isAdmin(userId)) return all;
    const assigned = await this.isAssignedToAudit(rowId, userId);
    return all.filter((c) => {
      const by = (c as any).built_by;
      return by === userId || ((by === null || by === undefined) && assigned);
    });
  }

  /** Builds the checklist and records the auditor who built it. */
  async buildForUser(
    rowId: number,
    userId: number,
    dto: { standard_id: number; template_item_ids: number[] },
  ) {
    const standardId = Number(dto.standard_id);
    const scope = await this.auditScope(rowId);
    if (!scope.has(standardId)) throw new BadRequestException('That standard is not part of this audit.');

    const ids = [...new Set((dto.template_item_ids ?? []).map(Number).filter((n) => Number.isInteger(n) && n > 0))];
    const allowed = new Set((await this.availableForAudit(rowId, userId)).flatMap((t) => t.items.map((i) => i.id)));
    if (ids.some((id) => !allowed.has(id))) {
      throw new ForbiddenException('Some selected items are not available to you for this audit.');
    }

    const cl = await this.checklists.buildChecklist(rowId, standardId, ids, userId);
    await this.ds.getRepository(AuditChecklist).update({ id: cl.id }, { built_by: userId } as any);
    return { ...cl, built_by: userId };
  }

  // ── review step: tick / untick items on a draft ─────────────────

  /** Draft-only edits: the auditor who built the checklist, or a coordinator / super-admin. */
  private async assertDraftEdit(checklistId: number, userId: number): Promise<AuditChecklist> {
    const cl = await this.ds.getRepository(AuditChecklist).findOne({ where: { id: checklistId } });
    if (!cl) throw new NotFoundException('Checklist not found');
    if (cl.status !== AuditChecklistStatus.DRAFT || cl.client_notified_at) {
      throw new BadRequestException('This checklist was already sent to the client, so its items can no longer change.');
    }
    if (!((await this.isAdmin(userId)) || (cl as any).built_by === userId)) {
      throw new ForbiddenException('Only the auditor who built this checklist, or a coordinator, can change its items.');
    }
    return cl;
  }

  async addDraftItems(userId: number, checklistId: number, templateItemIds: number[]) {
    await this.assertDraftEdit(checklistId, userId);
    const ids = [...new Set((templateItemIds ?? []).map(Number).filter((n) => Number.isInteger(n) && n > 0))];
    if (!ids.length) throw new BadRequestException('Choose at least one item.');
    const repo = this.ds.getRepository(AuditChecklistItem);
    const have = new Set((await repo.find({ where: { audit_checklist_id: checklistId } })).map((i) => i.template_item_id));
    const toAdd = ids.filter((id) => !have.has(id));
    if (toAdd.length) {
      const found = await this.ds.getRepository(ChecklistTemplateItem).find({ where: { id: In(toAdd) } });
      if (found.length !== toAdd.length) throw new BadRequestException('One or more items no longer exist.');
      await repo.save(
        toAdd.map((tid) =>
          repo.create({ audit_checklist_id: checklistId, template_item_id: tid, status: ChecklistItemStatus.MISSING }),
        ),
      );
    }
    return { added: toAdd.length };
  }

  async removeDraftItem(userId: number, checklistId: number, templateItemId: number) {
    await this.assertDraftEdit(checklistId, userId);
    const repo = this.ds.getRepository(AuditChecklistItem);
    const rows = await repo.find({ where: { audit_checklist_id: checklistId } });
    const target = rows.find((r) => r.template_item_id === templateItemId);
    if (!target) throw new NotFoundException('That item is not on this checklist.');
    if (rows.length <= 1) {
      throw new BadRequestException('A checklist needs at least one item. Discard the draft instead.');
    }
    await repo.remove(target);
    return { removed: true };
  }
}