<?php
/**
 * CompanyDocuments — central library of reusable company certificates + standard docs.
 *
 * Anyone can upload (CRM typically owns this), but access is restricted by DEFAULT
 * to Administrator + Finance Manager + Executive (auto-granted on insert). The uploader's
 * own role is also auto-granted. Admin / FM / Exec can then grant view access to other
 * user_groups via share() / revoke().
 *
 * Doc types are pulled from `tender_doc_types` (same catalog used for tender requirements),
 * so a Company doc of type BRELA_CERT can be surfaced when a tender requires BRELA_CERT.
 */
class CompanyDocuments extends Model
{
    /** Roles that get default view access to every uploaded document. */
    public const DEFAULT_ACCESS_ROLES = ['Administrator', 'Finance Manager', 'Executive'];

    /**
     * All documents visible to a specific user group (respects access rules).
     * $userGroupName is trusted (came from $_SESSION['user_group_name']).
     */
    public function listVisible(string $userGroupName, array $filter = []): array
    {
        $db = new DB();
        $where = ['cd.deleted = 0'];
        $params = [];

        // Access filter: doc must have an access row for this user's group OR user is in a default-access role.
        if (!in_array($userGroupName, self::DEFAULT_ACCESS_ROLES, true)) {
            $where[] = "EXISTS (SELECT 1 FROM company_document_access cda
                                  JOIN user_groups ug ON ug.c0d = cda.user_group_id
                                 WHERE cda.doc_id = cd.c0d AND ug.user_group_name = :ugn)";
            $params[':ugn'] = $userGroupName;
        }

        if (!empty($filter['status']))     { $where[] = 'cd.status = :st';         $params[':st'] = $filter['status']; }
        if (!empty($filter['expiring']))   { $where[] = 'cd.expiry_date IS NOT NULL AND cd.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY) AND cd.status = "CURRENT"'; }
        if (!empty($filter['expired']))    { $where[] = '(cd.expiry_date IS NOT NULL AND cd.expiry_date < CURDATE()) OR cd.status = "EXPIRED"'; }
        if (!empty($filter['q'])) {
            $where[] = '(cd.doc_name LIKE :q OR cd.ref_no LIKE :q OR cd.doc_type_code LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }

        return $db->mySelect(
            "SELECT cd.*, tdt.type_label
               FROM company_documents cd
          LEFT JOIN tender_doc_types tdt ON tdt.type_code = cd.doc_type_code
              WHERE " . implode(' AND ', $where) . "
              ORDER BY cd.status = 'CURRENT' DESC, cd.expiry_date IS NULL, cd.expiry_date ASC, cd.doc_name",
            $params
        ) ?: [];
    }

    public function find(int $id, string $userGroupName): ?array
    {
        // Access check inside SQL
        $db = new DB();
        $params = [':id' => $id];
        $accessClause = in_array($userGroupName, self::DEFAULT_ACCESS_ROLES, true) ? '1=1'
            : "EXISTS (SELECT 1 FROM company_document_access cda JOIN user_groups ug ON ug.c0d = cda.user_group_id WHERE cda.doc_id = cd.c0d AND ug.user_group_name = :ugn)";
        if ($accessClause !== '1=1') $params[':ugn'] = $userGroupName;
        $r = $db->mySelect(
            "SELECT cd.*, tdt.type_label
               FROM company_documents cd
          LEFT JOIN tender_doc_types tdt ON tdt.type_code = cd.doc_type_code
              WHERE cd.c0d = :id AND cd.deleted = 0 AND $accessClause",
            $params
        );
        return $r[0] ?? null;
    }

    /** Groups currently granted access to this doc (with the granter). */
    public function accessGrants(int $docId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT cda.*, ug.user_group_name
               FROM company_document_access cda
               JOIN user_groups ug ON ug.c0d = cda.user_group_id
              WHERE cda.doc_id = :id
              ORDER BY ug.user_group_name",
            [':id' => $docId]
        ) ?: [];
    }

    /** Groups NOT yet granted (for the share dropdown). */
    public function ungrantedGroups(int $docId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT ug.c0d, ug.user_group_name
               FROM user_groups ug
              WHERE NOT EXISTS (SELECT 1 FROM company_document_access cda WHERE cda.doc_id = :id AND cda.user_group_id = ug.c0d)
              ORDER BY ug.user_group_name",
            [':id' => $docId]
        ) ?: [];
    }

    /**
     * Insert a new document. Auto-grants view access to DEFAULT_ACCESS_ROLES + the uploader's own role.
     */
    public function create(array $d, int $userId, string $userName, string $userGroupName): int
    {
        $db = new DB();
        $expiry = !empty($d['expiry_date']) ? (string)$d['expiry_date'] : null;
        $status = 'CURRENT';
        if ($expiry !== null && $expiry < date('Y-m-d')) $status = 'EXPIRED';

        $fb = $db->insertUpdate(
            "INSERT INTO company_documents
                (doc_type_code, doc_name, ref_no, issue_date, expiry_date, status, notes,
                 uploaded_by, uploaded_by_name, uploaded_by_role, created_on)
             VALUES (:tc, :nm, :rf, :id, :ed, :st, :nt, :u, :un, :ur, NOW())",
            [
                ':tc' => (string)($d['doc_type_code'] ?? ''),
                ':nm' => (string)($d['doc_name'] ?? ''),
                ':rf' => (string)($d['ref_no'] ?? ''),
                ':id' => !empty($d['issue_date']) ? (string)$d['issue_date'] : null,
                ':ed' => $expiry,
                ':st' => $status,
                ':nt' => (string)($d['notes'] ?? ''),
                ':u'  => $userId,
                ':un' => $userName,
                ':ur' => $userGroupName,
            ]
        );
        $docId = (int)($fb['last_id'] ?? 0);

        // Default access: FM + Exec + Admin (always). Plus the uploader's own role if different.
        $defaultRoles = self::DEFAULT_ACCESS_ROLES;
        if (!in_array($userGroupName, $defaultRoles, true)) $defaultRoles[] = $userGroupName;

        $roleRows = $db->mySelect(
            "SELECT c0d FROM user_groups WHERE user_group_name IN ('" . implode("','", array_map('addslashes', $defaultRoles)) . "')",
            []
        ) ?: [];
        foreach ($roleRows as $r) {
            $db->insertUpdate(
                "INSERT IGNORE INTO company_document_access (doc_id, user_group_id, granted_by, granted_by_name, granted_on)
                 VALUES (:d, :g, :u, :un, NOW())",
                [':d' => $docId, ':g' => (int)$r['c0d'], ':u' => $userId, ':un' => 'auto (default access)']
            );
        }

        return $docId;
    }

    public function update(int $id, array $d, int $userId): void
    {
        $db = new DB();
        $expiry = !empty($d['expiry_date']) ? (string)$d['expiry_date'] : null;
        $status = (string)($d['status'] ?? 'CURRENT');
        // Auto-flip to EXPIRED if the date says so
        if ($status === 'CURRENT' && $expiry !== null && $expiry < date('Y-m-d')) $status = 'EXPIRED';

        $db->insertUpdate(
            "UPDATE company_documents SET
                doc_type_code = :tc, doc_name = :nm, ref_no = :rf,
                issue_date = :id, expiry_date = :ed, status = :st, notes = :nt,
                edited_by = :u, edited_on = NOW()
              WHERE c0d = :did",
            [
                ':tc' => (string)($d['doc_type_code'] ?? ''),
                ':nm' => (string)($d['doc_name'] ?? ''),
                ':rf' => (string)($d['ref_no'] ?? ''),
                ':id' => !empty($d['issue_date']) ? (string)$d['issue_date'] : null,
                ':ed' => $expiry,
                ':st' => $status,
                ':nt' => (string)($d['notes'] ?? ''),
                ':u'  => $userId,
                ':did'=> $id,
            ]
        );
    }

    /** Grant a group access. Only default-access roles can share. */
    public function share(int $docId, int $userGroupId, int $granterUserId, string $granterName, string $granterRole): void
    {
        if (!in_array($granterRole, self::DEFAULT_ACCESS_ROLES, true)) {
            throw new RuntimeException('Only Administrator / Finance Manager / Executive can grant access.');
        }
        $db = new DB();
        $db->insertUpdate(
            "INSERT IGNORE INTO company_document_access (doc_id, user_group_id, granted_by, granted_by_name, granted_on)
             VALUES (:d, :g, :u, :un, NOW())",
            [':d' => $docId, ':g' => $userGroupId, ':u' => $granterUserId, ':un' => $granterName]
        );
    }

    public function revoke(int $docId, int $userGroupId, string $granterRole): void
    {
        if (!in_array($granterRole, self::DEFAULT_ACCESS_ROLES, true)) {
            throw new RuntimeException('Only Administrator / Finance Manager / Executive can revoke access.');
        }
        // Guard: default roles can't have their access revoked (they always see everything)
        $db = new DB();
        $g = $db->mySelect("SELECT user_group_name FROM user_groups WHERE c0d = :id", [':id' => $userGroupId]);
        if (!empty($g) && in_array($g[0]['user_group_name'], self::DEFAULT_ACCESS_ROLES, true)) {
            throw new RuntimeException('Cannot revoke the default access roles (Administrator / Finance Manager / Executive).');
        }
        $stmt = $db->dBCxn()->prepare("DELETE FROM company_document_access WHERE doc_id = ? AND user_group_id = ?");
        $stmt->execute([$docId, $userGroupId]);
    }

    /** KPI card counts for the index page (respecting visibility). */
    public function counts(string $userGroupName): array
    {
        $rows = $this->listVisible($userGroupName);
        $counts = ['total' => 0, 'current' => 0, 'superseded' => 0, 'expiring60' => 0, 'expired' => 0];
        $today = date('Y-m-d');
        $in60  = date('Y-m-d', strtotime('+60 days'));
        foreach ($rows as $r) {
            $counts['total']++;
            if ($r['status'] === 'CURRENT')     $counts['current']++;
            if ($r['status'] === 'SUPERSEDED')  $counts['superseded']++;
            if (!empty($r['expiry_date']) && $r['expiry_date'] < $today) { $counts['expired']++; continue; }
            if (!empty($r['expiry_date']) && $r['expiry_date'] <= $in60 && $r['status'] === 'CURRENT') $counts['expiring60']++;
        }
        return $counts;
    }

    /** Rows for the "Action Needed" panel: expiring soon or already expired. */
    public function actionNeeded(string $userGroupName, int $limit = 12): array
    {
        $rows = $this->listVisible($userGroupName);
        $today = date('Y-m-d');
        $in60  = date('Y-m-d', strtotime('+60 days'));
        $out = [];
        foreach ($rows as $r) {
            if (empty($r['expiry_date'])) continue;
            if ($r['expiry_date'] < $today || $r['expiry_date'] <= $in60) $out[] = $r;
            if (count($out) >= $limit) break;
        }
        return $out;
    }

    /**
     * Catalog of doc types the user can pick from — from tender_doc_types.
     * Grouped by file_cat so the dropdown is scannable.
     */
    public function docTypeCatalog(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT type_code, type_label, file_cat, is_statutory
               FROM tender_doc_types
              WHERE is_active = 1 AND deleted = 0
              ORDER BY file_cat, sort_order, type_label",
            []
        ) ?: [];
    }
}
