FileUse.ps1

<#
.SYNOPSIS
    Personal file-use index: path + DTS + open count.
    Watches the Recent Items folder (SHAddToRecentDocs), NOT the whole disk.

.DESCRIPTION
    Windows already writes a .lnk into %APPDATA%\Microsoft\Windows\Recent
    when Explorer or a common file dialog opens a file on purpose.
    This script treats that as the "I clicked it" signal, upserts SQLite:

        C:\Users\<you>\FileUse\fileuse.sqlite

    It does NOT FileSystemWatcher your Documents/Downloads trees.
    That would log Defender, Search, OneDrive, and thumbnail noise —
    i.e. the event log you do not want a copy of.

.EXAMPLE
    .\FileUse.ps1 -Init
    .\FileUse.ps1 -Harvest
    .\FileUse.ps1 -Watch
    .\FileUse.ps1 -Top 40
    .\FileUse.ps1 -Top 40 -Frecency
    .\FileUse.ps1 -InstallTask
    .\FileUse.ps1 -UninstallTask
#>
[CmdletBinding()]
param(
    [switch]$Init,
    [switch]$Harvest,
    [switch]$Watch,
    [int]$Top = 0,
    [switch]$Frecency,
    [string]$Like,
    [switch]$InstallTask,
    [switch]$UninstallTask,
    [int]$PollSeconds = 2,
    [string]$DbPath
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

# ---------------------------------------------------------------------------
# Paths
# ---------------------------------------------------------------------------
if (-not $DbPath) {
    $root = Join-Path $env:USERPROFILE 'FileUse'
    $DbPath = Join-Path $root 'fileuse.sqlite'
}
$root = Split-Path -Parent $DbPath
$recentDir = Join-Path $env:APPDATA 'Microsoft\Windows\Recent'
$logPath = Join-Path $root 'fileuse.log'

# ---------------------------------------------------------------------------
# SQLite helpers (PSSQLite if present; else sqlite3.exe)
# ---------------------------------------------------------------------------
function Test-Pssqlite {
    return [bool](Get-Module -ListAvailable -Name PSSQLite)
}

function Ensure-SqliteTool {
    if (Test-Pssqlite) {
        Import-Module PSSQLite -ErrorAction Stop
        return 'pssqlite'
    }
    $sqlite3 = Get-Command sqlite3.exe -ErrorAction SilentlyContinue
    if ($sqlite3) { return 'sqlite3' }

    throw @"
No SQLite backend found.

Install one of:
  Install-Module PSSQLite -Scope CurrentUser
  winget install SQLite.SQLite

Then re-run.
"@
}

$script:SqliteBackend = $null

function Invoke-FileUseSql {
    param(
        [Parameter(Mandatory)][string]$Query,
        [hashtable]$SqlParameters
    )
    if (-not $script:SqliteBackend) { $script:SqliteBackend = Ensure-SqliteTool }

    if ($script:SqliteBackend -eq 'pssqlite') {
        if ($SqlParameters) {
            return Invoke-SqliteQuery -DataSource $DbPath -Query $Query -SqlParameters $SqlParameters
        }
        return Invoke-SqliteQuery -DataSource $DbPath -Query $Query
    }

    # sqlite3.exe fallback — no parameterized queries; sanitize single quotes
    $q = $Query
    if ($SqlParameters) {
        foreach ($k in $SqlParameters.Keys) {
            $val = [string]$SqlParameters[$k]
            $val = $val.Replace("'", "''")
            $q = $q -replace "@$k\b", "'$val'"
        }
    }
    $raw = & sqlite3.exe -header -csv $DbPath $q 2>&1
    if ($LASTEXITCODE -ne 0) { throw "sqlite3 failed: $raw" }
    if (-not $raw) { return @() }
    return $raw | ConvertFrom-Csv
}

function Write-FileUseLog {
    param([string]$Message)
    $line = '{0:yyyy-MM-dd HH:mm:ss} {1}' -f (Get-Date), $Message
    try {
        Add-Content -Path $logPath -Value $line -Encoding UTF8
    } catch { }
}

# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------
function Initialize-FileUseDb {
    if (-not (Test-Path $root)) {
        New-Item -ItemType Directory -Path $root -Force | Out-Null
    }

    $ddl = @'
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

CREATE TABLE IF NOT EXISTS files (
    path          TEXT PRIMARY KEY COLLATE NOCASE,
    ext           TEXT,
    first_seen    TEXT NOT NULL,
    last_seen     TEXT NOT NULL,
    open_count    INTEGER NOT NULL DEFAULT 0,
    last_lnk_mtime INTEGER,
    last_size     INTEGER
);

CREATE TABLE IF NOT EXISTS opens (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    path       TEXT NOT NULL COLLATE NOCASE,
    opened_at  TEXT NOT NULL,
    source     TEXT NOT NULL,
    lnk_mtime  INTEGER
);

CREATE INDEX IF NOT EXISTS ix_files_count ON files(open_count DESC);
CREATE INDEX IF NOT EXISTS ix_files_last  ON files(last_seen DESC);
CREATE INDEX IF NOT EXISTS ix_opens_dts   ON opens(opened_at DESC);
CREATE INDEX IF NOT EXISTS ix_opens_path  ON opens(path);
CREATE UNIQUE INDEX IF NOT EXISTS ix_opens_lnk
    ON opens(path, lnk_mtime)
    WHERE lnk_mtime > 0;
'@

    if (-not $script:SqliteBackend) { $script:SqliteBackend = Ensure-SqliteTool }

    if ($script:SqliteBackend -eq 'pssqlite') {
        Invoke-SqliteQuery -DataSource $DbPath -Query $ddl | Out-Null
    } else {
        $ddl | & sqlite3.exe $DbPath
        if ($LASTEXITCODE -ne 0) { throw 'sqlite3 schema init failed' }
    }
    Write-FileUseLog "Initialized $DbPath"
}

# ---------------------------------------------------------------------------
# Resolve a Recent .lnk to a real path
# ---------------------------------------------------------------------------
function Resolve-RecentLnk {
    param([string]$LnkPath)
    try {
        $sh = New-Object -ComObject WScript.Shell
        $sc = $sh.CreateShortcut($LnkPath)
        $target = [string]$sc.TargetPath
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($sc)
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($sh)
        if ([string]::IsNullOrWhiteSpace($target)) { return $null }
        return $target
    } catch {
        return $null
    }
}

# ---------------------------------------------------------------------------
# Record one purposeful open
# ---------------------------------------------------------------------------
function Add-FileUseOpen {
    param(
        [Parameter(Mandatory)][string]$Path,
        [datetime]$When = $(Get-Date),
        [string]$Source = 'recent-lnk',
        [int64]$LnkMtime = 0,
        [int64]$Size = 0
    )

    $pathNorm = $Path.Trim()
    if (-not $pathNorm) { return $false }

    $ext = [System.IO.Path]::GetExtension($pathNorm).TrimStart('.').ToLowerInvariant()
    $dts = $When.ToString('yyyy-MM-dd HH:mm:ss')

    # Skip if we already recorded this exact shortcut revision for this path.
    # Checking the event ledger (rather than files.last_lnk_mtime) also handles
    # two differently named Recent shortcuts that resolve to the same target.
    if ($LnkMtime -gt 0) {
        $dup = Invoke-FileUseSql -Query 'SELECT 1 AS x FROM opens WHERE path = @p AND lnk_mtime = @m LIMIT 1' -SqlParameters @{ p = $pathNorm; m = $LnkMtime }
        if ($dup) { return $false }
    }

    Invoke-FileUseSql -Query @'
INSERT INTO files (path, ext, first_seen, last_seen, open_count, last_lnk_mtime, last_size)
VALUES (@p, @e, @d, @d, 1, @m, @s)
ON CONFLICT(path) DO UPDATE SET
    last_seen      = excluded.last_seen,
    open_count     = open_count + 1,
    last_lnk_mtime = excluded.last_lnk_mtime,
    last_size      = COALESCE(excluded.last_size, last_size),
    ext            = COALESCE(excluded.ext, ext);
'@ -SqlParameters @{
        p = $pathNorm
        e = $ext
        d = $dts
        m = $LnkMtime
        s = $Size
    } | Out-Null

    Invoke-FileUseSql -Query @'
INSERT INTO opens (path, opened_at, source, lnk_mtime)
VALUES (@p, @d, @src, @m);
'@ -SqlParameters @{
        p   = $pathNorm
        d   = $dts
        src = $Source
        m   = $LnkMtime
    } | Out-Null

    return $true
}

# ---------------------------------------------------------------------------
# Harvest every .lnk currently in Recent\
# ---------------------------------------------------------------------------
function Invoke-FileUseHarvest {
    if (-not (Test-Path $recentDir)) {
        Write-Warning "Recent folder not found: $recentDir"
        return 0
    }

    $added = 0
    Get-ChildItem -LiteralPath $recentDir -Filter '*.lnk' -File -ErrorAction SilentlyContinue |
        ForEach-Object {
            $target = Resolve-RecentLnk -LnkPath $_.FullName
            if (-not $target) { return }

            $mtime = [int64]($_.LastWriteTimeUtc.Ticks)
            $size  = 0
            if (Test-Path -LiteralPath $target) {
                try { $size = [int64](Get-Item -LiteralPath $target -ErrorAction Stop).Length } catch { }
            }

            if (Add-FileUseOpen -Path $target -When $_.LastWriteTime -Source 'recent-lnk' -LnkMtime $mtime -Size $size) {
                $added++
            }
        }

    Write-FileUseLog "Harvest added $added new opens from $recentDir"
    return $added
}

# ---------------------------------------------------------------------------
# Live watcher — Recent\ only
# ---------------------------------------------------------------------------
function Start-FileUseWatch {
    if (-not (Test-Path $recentDir)) {
        throw "Recent folder not found: $recentDir"
    }

    Write-Host "Watching $recentDir"
    Write-Host "Database $DbPath"
    Write-Host "Ctrl+C to stop."

    $fsw = New-Object System.IO.FileSystemWatcher
    $fsw.Path = $recentDir
    $fsw.Filter = '*.lnk'
    $fsw.IncludeSubdirectories = $false
    $fsw.NotifyFilter = [IO.NotifyFilters]::FileName -bor [IO.NotifyFilters]::LastWrite
    $fsw.EnableRaisingEvents = $true

    $handler = {
        $name = $Event.SourceEventArgs.Name
        $full = $Event.SourceEventArgs.FullPath
        if (-not $name -or -not $name.EndsWith('.lnk', [StringComparison]::OrdinalIgnoreCase)) { return }
        # DestList compound files live in subfolders we are not watching,
        # but skip anything that is not a real shortcut just in case.
        Start-Sleep -Milliseconds 150  # let Explorer finish writing the lnk
        try {
            $item = Get-Item -LiteralPath $full -ErrorAction Stop
            $target = Resolve-RecentLnk -LnkPath $full
            if (-not $target) { return }
            $mtime = [int64]$item.LastWriteTimeUtc.Ticks
            $size = 0
            if (Test-Path -LiteralPath $target) {
                try { $size = [int64](Get-Item -LiteralPath $target).Length } catch { }
            }
            if (Add-FileUseOpen -Path $target -When $item.LastWriteTime -Source 'recent-lnk' -LnkMtime $mtime -Size $size) {
                Write-Host ("{0:HH:mm:ss} + {1}" -f (Get-Date), $target)
                Write-FileUseLog "open $target"
            }
        } catch {
            Write-FileUseLog "watch error $($_.Exception.Message)"
        }
    }

    $subs = @(
        Register-ObjectEvent $fsw Created -Action $handler
        Register-ObjectEvent $fsw Changed -Action $handler
        Register-ObjectEvent $fsw Renamed -Action $handler
    )

    try {
        while ($true) { Start-Sleep -Seconds $PollSeconds }
    } finally {
        $subs | ForEach-Object { Unregister-Event -SourceIdentifier $_.Name -ErrorAction SilentlyContinue }
        $fsw.EnableRaisingEvents = $false
        $fsw.Dispose()
    }
}

# ---------------------------------------------------------------------------
# Queries
# ---------------------------------------------------------------------------
function Get-FileUseTop {
    param(
        [int]$N = 40,
        [switch]$UseFrecency,
        [string]$Pattern
    )

    $where = ''
    $parms = @{ n = $N }
    if ($Pattern) {
        $where = 'WHERE path LIKE @pat'
        $parms.pat = $Pattern
    }

    if ($UseFrecency) {
        # Recency-weighted count: recent opens count more.
        # Half-life ~14 days. Pure SQL so he can tweak the constant.
        $sql = @"
SELECT
    f.path,
    f.ext,
    f.open_count,
    f.first_seen,
    f.last_seen,
    ROUND(
        (SELECT COALESCE(SUM(
            EXP(-0.0495 * (JULIANDAY('now','localtime') - JULIANDAY(o.opened_at)))
        ), 0)
         FROM opens o WHERE o.path = f.path)
    , 3) AS frecency
FROM files f
$where
ORDER BY frecency DESC, f.open_count DESC
LIMIT @n;
"@
    } else {
        $sql = @"
SELECT path, ext, open_count, first_seen, last_seen
FROM files
$where
ORDER BY last_seen DESC, open_count DESC
LIMIT @n;
"@
    }

    Invoke-FileUseSql -Query $sql -SqlParameters $parms
}

# ---------------------------------------------------------------------------
# Scheduled task — harvest every 2 minutes in the user session
# ---------------------------------------------------------------------------
function Install-FileUseTask {
    $scriptPath = $PSCommandPath
    if (-not $scriptPath) { $scriptPath = $MyInvocation.MyCommand.Path }
    if (-not $scriptPath) { throw 'Cannot locate FileUse.ps1 to register the task.' }

    $hostExe = (Get-Process -Id $PID).Path
    if (-not $hostExe) { throw 'Cannot locate the current PowerShell executable.' }
    $action = New-ScheduledTaskAction -Execute $hostExe -Argument (
        "-NoProfile -WindowStyle Hidden -ExecutionPolicy Bypass -File `"$scriptPath`" -Harvest"
    )
    $trigger = New-ScheduledTaskTrigger -Once -At (Get-Date).AddMinutes(1) `
        -RepetitionInterval (New-TimeSpan -Minutes 2) `
        -RepetitionDuration ([TimeSpan]::MaxValue)
    $settings = New-ScheduledTaskSettingsSet -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries `
        -StartWhenAvailable -MultipleInstances IgnoreNew
    $principal = New-ScheduledTaskPrincipal -UserId $env:USERNAME -LogonType Interactive -RunLevel Limited

    Register-ScheduledTask -TaskName 'FileUse-Harvest' -Action $action -Trigger $trigger `
        -Settings $settings -Principal $principal -Force | Out-Null
    Write-Host "Registered scheduled task FileUse-Harvest (every 2 min)."
    Write-FileUseLog 'Installed scheduled task FileUse-Harvest'
}

function Uninstall-FileUseTask {
    Unregister-ScheduledTask -TaskName 'FileUse-Harvest' -Confirm:$false -ErrorAction SilentlyContinue
    Write-Host 'Removed scheduled task FileUse-Harvest.'
}

# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------
$did = $false

if ($Init -or $Harvest -or $Watch -or $Top -gt 0 -or $InstallTask) {
    Initialize-FileUseDb
}

if ($Init) {
    Write-Host "Database ready: $DbPath"
    $did = $true
}

if ($Harvest) {
    $n = Invoke-FileUseHarvest
    Write-Host "Harvested $n new open(s)."
    $did = $true
}

if ($InstallTask) {
    Install-FileUseTask
    $did = $true
}

if ($UninstallTask) {
    Uninstall-FileUseTask
    $did = $true
}

if ($Top -gt 0) {
    $pat = $null
    if ($Like) { $pat = $Like }
    Get-FileUseTop -N $Top -UseFrecency:$Frecency -Pattern $pat |
        Format-Table -AutoSize
    $did = $true
}

if ($Watch) {
    $did = $true
    Start-FileUseWatch
}

if (-not $did) {
    Write-Host @"
FileUse — personal open-count index

  .\FileUse.ps1 -Init
  .\FileUse.ps1 -Harvest
  .\FileUse.ps1 -Watch
  .\FileUse.ps1 -Top 40
  .\FileUse.ps1 -Top 40 -Frecency
  .\FileUse.ps1 -Top 40 -Like '%\Documents\%'
  .\FileUse.ps1 -InstallTask

Database: $DbPath
Sensor:   $recentDir\*.lnk   (not the whole disk)
"@
}