FileUse-Tenure.ps1

<#
.SYNOPSIS
    Promotion / tenure / retirement for The Bench — the folder that opens at boot.

.DESCRIPTION
    Membership function for the icon board. Not a sort.

      1st qualifying open  -> enlist,  48-hour watch + tenure
      next open next day   -> promote, 5-day watch + tenure
      next open next day   -> promote, 14-day watch + tenure
      next open next day   -> promote, 30-day watch + tenure
      open while on 30-day -> renew 30 days
      window expires       -> retire (thank you for your service)
      open after retire    -> re-enlist at 48 hours, no rank carryover

    Extra clicks on the SAME local calendar day as the last promotion
    only refresh last_seen. That stops a 4-click burst from minting a
    30-day veteran in two minutes. Tenure is time served, not click rate.

    Sensor remains FileUse.ps1 (Recent\*.lnk). This script consumes the
    opens table in chronological order.

.EXAMPLE
    .\FileUse-Tenure.ps1 -Apply -Retire -Roster
    .\FileUse-Tenure.ps1 -Apply -Retire -SyncDock
    .\FileUse-Tenure.ps1 -Roster
    .\FileUse-Tenure.ps1 -History
#>
[CmdletBinding()]
param(
    [switch]$Apply,
    [switch]$Retire,
    [switch]$Roster,
    [switch]$History,
    [switch]$SyncDock,
    [switch]$Open,
    [switch]$InstallStartup,
    [switch]$UninstallStartup,
    [int]$Top = 48,
    [int]$MinHoursBetweenPromote = 24,
    [string]$DbPath,
    [string]$DockPath,
    [string]$DischargedPath
)

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

if (-not $DbPath)         { $DbPath         = Join-Path $env:USERPROFILE 'FileUse\fileuse.sqlite' }
if (-not $DockPath)       { $DockPath       = Join-Path $env:USERPROFILE 'FileUse\The Bench' }
if (-not $DischargedPath) { $DischargedPath = Join-Path $env:USERPROFILE 'FileUse\Discharged' }

$startupDir = Join-Path $env:APPDATA 'Microsoft\Windows\Start Menu\Programs\Startup'
$startupLnk = Join-Path $startupDir 'The Bench.lnk'

# Stage 1..4 durations
$script:StageHours = @{
    1 = 48
    2 = 5 * 24
    3 = 14 * 24
    4 = 30 * 24
}
$script:StageName = @{
    1 = '48h probation'
    2 = '5d'
    3 = '14d'
    4 = '30d veteran'
}

function Test-Pssqlite { [bool](Get-Module -ListAvailable -Name PSSQLite) }

$script:Backend = $null
$script:Connection = $null
function Ensure-Sqlite {
    if ($script:Backend) { return }
    if (Test-Pssqlite) {
        Import-Module PSSQLite -ErrorAction Stop
        $script:Backend = 'pssqlite'
        return
    }
    if (Get-Command sqlite3.exe -ErrorAction SilentlyContinue) {
        $script:Backend = 'sqlite3'
        return
    }
    throw 'Install-Module PSSQLite -Scope CurrentUser   (or sqlite3.exe)'
}

function Invoke-TSql {
    param([Parameter(Mandatory)][string]$Query, [hashtable]$SqlParameters)
    Ensure-Sqlite
    if ($script:Backend -eq 'pssqlite') {
        $invokeArgs = @{ Query = $Query }
        if ($script:Connection) {
            $invokeArgs.SQLiteConnection = $script:Connection
        } else {
            $invokeArgs.DataSource = $DbPath
        }
        if ($SqlParameters) {
            $invokeArgs.SqlParameters = $SqlParameters
        }
        return Invoke-SqliteQuery @invokeArgs
    }
    $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 Initialize-TenureSchema {
    if (-not (Test-Path -LiteralPath $DbPath)) {
        throw "No database at $DbPath. Run FileUse.ps1 -Init / -Harvest first."
    }
    Invoke-TSql -Query @'
CREATE TABLE IF NOT EXISTS tenure (
    path          TEXT PRIMARY KEY COLLATE NOCASE,
    stage         INTEGER NOT NULL,
    window_start  TEXT NOT NULL,
    window_end    TEXT NOT NULL,
    last_click    TEXT NOT NULL,
    last_promote  TEXT NOT NULL,
    retired_at    TEXT
);
CREATE TABLE IF NOT EXISTS tenure_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    path       TEXT NOT NULL,
    dts        TEXT NOT NULL,
    action     TEXT NOT NULL,
    stage      INTEGER,
    window_end TEXT,
    note       TEXT
);
CREATE TABLE IF NOT EXISTS tenure_meta (
    k TEXT PRIMARY KEY,
    v TEXT
);
INSERT OR IGNORE INTO tenure_meta (k, v) VALUES ('last_open_id', '0');
'@ | Out-Null
}

function Write-TenureLog {
    param([string]$Path, [string]$When, [string]$Action, [int]$Stage, [string]$WindowEnd, [string]$Note)
    Invoke-TSql -Query @'
INSERT INTO tenure_log (path, dts, action, stage, window_end, note)
VALUES (@p, @d, @a, @s, @w, @n);
'@ -SqlParameters @{
        p = $Path; d = $When; a = $Action
        s = $Stage; w = $WindowEnd; n = $Note
    } | Out-Null
}

function Get-WindowEnd {
    param([datetime]$From, [int]$Stage)
    $hours = [int]$script:StageHours[$Stage]
    return $From.AddHours($hours)
}

function Convert-Dts {
    param([string]$S)
    return [datetime]::ParseExact($S, 'yyyy-MM-dd HH:mm:ss', [Globalization.CultureInfo]::InvariantCulture)
}

function Format-Dts {
    param([datetime]$D)
    return $D.ToString('yyyy-MM-dd HH:mm:ss')
}

function Invoke-TenureClick {
    param([string]$Path, [datetime]$When)

    $whenS = Format-Dts $When
    $row = Invoke-TSql -Query 'SELECT * FROM tenure WHERE path = @p' -SqlParameters @{ p = $Path } | Select-Object -First 1

    $active = $false
    if ($row -and -not $row.retired_at) {
        $end = Convert-Dts $row.window_end
        if ($When -le $end) { $active = $true }
    }

    if (-not $active) {
        $end = Get-WindowEnd -From $When -Stage 1
        $endS = Format-Dts $end
        Invoke-TSql -Query @'
INSERT INTO tenure (path, stage, window_start, window_end, last_click, last_promote, retired_at)
VALUES (@p, 1, @d, @e, @d, @d, NULL)
ON CONFLICT(path) DO UPDATE SET
    stage        = 1,
    window_start = excluded.window_start,
    window_end   = excluded.window_end,
    last_click   = excluded.last_click,
    last_promote = excluded.last_promote,
    retired_at   = NULL;
'@ -SqlParameters @{ p = $Path; d = $whenS; e = $endS } | Out-Null
        $note = if ($row) { 're-enlist after retirement' } else { 'first click' }
        Write-TenureLog -Path $Path -When $whenS -Action 'enlist' -Stage 1 -WindowEnd $endS -Note $note
        return
    }

    Invoke-TSql -Query 'UPDATE tenure SET last_click = @d WHERE path = @p' -SqlParameters @{ p = $Path; d = $whenS } | Out-Null

    $lastPromote = Convert-Dts $row.last_promote
    $hoursSince  = ($When - $lastPromote).TotalHours
    $newDay      = $When.Date -gt $lastPromote.Date
    $canPromote  = $newDay -or ($hoursSince -ge $MinHoursBetweenPromote)

    if (-not $canPromote) {
        Write-TenureLog -Path $Path -When $whenS -Action 'seen' -Stage ([int]$row.stage) -WindowEnd $row.window_end -Note 'same-day click, no promote'
        return
    }

    $stage = [int]$row.stage
    if ($stage -lt 4) {
        $stage++
        $end = Get-WindowEnd -From $When -Stage $stage
        $endS = Format-Dts $end
        Invoke-TSql -Query @'
UPDATE tenure
SET stage = @s, window_start = @d, window_end = @e, last_click = @d, last_promote = @d, retired_at = NULL
WHERE path = @p;
'@ -SqlParameters @{ p = $Path; s = $stage; d = $whenS; e = $endS } | Out-Null
        Write-TenureLog -Path $Path -When $whenS -Action 'promote' -Stage $stage -WindowEnd $endS -Note $script:StageName[$stage]
    } else {
        $end = Get-WindowEnd -From $When -Stage 4
        $endS = Format-Dts $end
        Invoke-TSql -Query @'
UPDATE tenure
SET window_start = @d, window_end = @e, last_click = @d, last_promote = @d, retired_at = NULL
WHERE path = @p;
'@ -SqlParameters @{ p = $Path; d = $whenS; e = $endS } | Out-Null
        Write-TenureLog -Path $Path -When $whenS -Action 'renew' -Stage 4 -WindowEnd $endS -Note '30d renewed'
    }
}

function Invoke-TenureApply {
    Initialize-TenureSchema
    Ensure-Sqlite

    $connection = $null
    $inTransaction = $false
    try {
        # Keep tenure changes and its replay watermark atomic. If processing is
        # interrupted, the next run safely starts from the same open event.
        if ($script:Backend -eq 'pssqlite') {
            $connection = New-SQLiteConnection -DataSource $DbPath
            $script:Connection = $connection
            Invoke-TSql -Query 'BEGIN IMMEDIATE;' | Out-Null
            $inTransaction = $true
        }

        $meta = Invoke-TSql -Query "SELECT v FROM tenure_meta WHERE k = 'last_open_id'" | Select-Object -First 1
        $lastId = 0
        if ($meta -and $meta.v) { [void][int]::TryParse([string]$meta.v, [ref]$lastId) }

        $rows = @(Invoke-TSql -Query 'SELECT id, path, opened_at FROM opens WHERE id > @i ORDER BY opened_at ASC, id ASC' -SqlParameters @{ i = $lastId })
        $n = 0
        $maxId = $lastId
        foreach ($r in $rows) {
            $id = [int]$r.id
            if ($id -gt $maxId) { $maxId = $id }
            $when = Convert-Dts ([string]$r.opened_at)
            Invoke-TenureClick -Path ([string]$r.path) -When $when
            $n++
        }
        Invoke-TSql -Query "UPDATE tenure_meta SET v = @v WHERE k = 'last_open_id'" -SqlParameters @{ v = [string]$maxId } | Out-Null

        if ($inTransaction) {
            Invoke-TSql -Query 'COMMIT;' | Out-Null
            $inTransaction = $false
        }
    } catch {
        if ($inTransaction) {
            try { Invoke-TSql -Query 'ROLLBACK;' | Out-Null } catch { }
        }
        throw
    } finally {
        $script:Connection = $null
        if ($connection) { $connection.Dispose() }
    }
    Write-Host "Applied $n open(s) to tenure."
}

function Invoke-TenureRetire {
    Initialize-TenureSchema
    $now = Format-Dts (Get-Date)
    $due = @(Invoke-TSql -Query "SELECT path, stage, window_end FROM tenure WHERE retired_at IS NULL AND window_end < @n" -SqlParameters @{ n = $now })
    foreach ($r in $due) {
        Invoke-TSql -Query "UPDATE tenure SET retired_at = @n WHERE path = @p" -SqlParameters @{ n = $now; p = [string]$r.path } | Out-Null
        Write-TenureLog -Path ([string]$r.path) -When $now -Action 'retire' -Stage ([int]$r.stage) -WindowEnd ([string]$r.window_end) -Note 'thank you for your service'
    }
    Write-Host ("Retired {0} item(s)." -f $due.Count)
}

function Get-TenureRoster {
    Initialize-TenureSchema
    $now = Format-Dts (Get-Date)
    Invoke-TSql -Query @'
SELECT
    t.stage,
    t.path,
    t.window_start,
    t.window_end,
    t.last_click,
    ROUND((JULIANDAY(t.window_end) - JULIANDAY(@n)) * 24, 1) AS hours_left
FROM tenure t
WHERE t.retired_at IS NULL AND t.window_end >= @n
ORDER BY t.stage DESC, t.window_end ASC;
'@ -SqlParameters @{ n = $now }
}

function Get-TenureHistory {
    Initialize-TenureSchema
    Invoke-TSql -Query 'SELECT dts, action, stage, path, note FROM tenure_log ORDER BY id DESC LIMIT 80'
}

function Get-SafeBaseName {
    param([string]$TargetPath)
    $base = [IO.Path]::GetFileNameWithoutExtension($TargetPath)
    if ([string]::IsNullOrWhiteSpace($base)) { $base = 'item' }
    foreach ($c in [IO.Path]::GetInvalidFileNameChars()) { $base = $base.Replace([string]$c, '_') }
    if ($base.Length -gt 42) { $base = $base.Substring(0, 42) }
    return $base
}

function Get-PathTag {
    param([string]$TargetPath)
    $sha = [Security.Cryptography.SHA256]::Create()
    try {
        $bytes = [Text.Encoding]::UTF8.GetBytes($TargetPath.ToUpperInvariant())
        $hash = $sha.ComputeHash($bytes)
        return ([BitConverter]::ToString($hash, 0, 4) -replace '-', '').ToLowerInvariant()
    } finally {
        $sha.Dispose()
    }
}

function Write-ToolShortcut {
    param(
        [Parameter(Mandatory)]$Shell,
        [Parameter(Mandatory)][string]$LnkPath,
        [Parameter(Mandatory)][string]$Target,
        [string]$Comment
    )
    $sc = $Shell.CreateShortcut($LnkPath)
    $sc.TargetPath = $Target
    if (Test-Path -LiteralPath $Target -PathType Container) {
        $sc.WorkingDirectory = $Target
    } else {
        $parent = [IO.Path]::GetDirectoryName($Target)
        if ($parent) { $sc.WorkingDirectory = $parent }
    }
    if ($Comment) { $sc.Description = $Comment }
    $sc.Save()
    [void][Runtime.InteropServices.Marshal]::ReleaseComObject($sc)
}

function Write-BenchDesktopIni {
    param([string]$Folder, [string]$DisplayName, [string]$Tip)
    $ini = Join-Path $Folder 'desktop.ini'
    $text = @"
[.ShellClassInfo]
ConfirmFileOp=0
LocalizedResourceName=$DisplayName
InfoTip=$Tip
IconResource=%SystemRoot%\System32\shell32.dll,165
"@
    Set-Content -LiteralPath $ini -Value $text -Encoding Unicode
    attrib +s +h $ini | Out-Null
    attrib +s $Folder | Out-Null
}

function Write-BenchRoster {
    param($OnDuty, $DischargedToday)
    $now = Get-Date
    $lines = New-Object System.Collections.Generic.List[string]
    [void]$lines.Add('THE BENCH')
    [void]$lines.Add('Finite hooks. Tenure applies. The tree is the wall cabinet.')
    [void]$lines.Add(('Written {0:yyyy-MM-dd HH:mm}' -f $now))
    [void]$lines.Add('')
    [void]$lines.Add('ON DUTY')
    [void]$lines.Add('-------')
    if (-not $OnDuty -or $OnDuty.Count -eq 0) {
        [void]$lines.Add('(empty bench — go use a tool)')
    } else {
        foreach ($r in $OnDuty) {
            $hours = 0.0
            [void][double]::TryParse([string]$r.hours_left, [ref]$hours)
            $days = [math]::Max(0, [math]::Round($hours / 24.0, 1))
            $label = $script:StageName[[int]$r.stage]
            $leaf  = [IO.Path]::GetFileName([string]$r.path)
            [void]$lines.Add(('{0,-16}  {1,6}d left  {2}' -f $label, $days, $leaf))
        }
    }
    [void]$lines.Add('')
    [void]$lines.Add('DISCHARGED THIS RUN')
    [void]$lines.Add('-------------------')
    if (-not $DischargedToday -or $DischargedToday.Count -eq 0) {
        [void]$lines.Add('(nobody sent to the drawer)')
    } else {
        foreach ($r in $DischargedToday) {
            $leaf = [IO.Path]::GetFileName([string]$r.path)
            [void]$lines.Add(('Thank you for your service.  {0}  (was {1})' -f $leaf, $script:StageName[[int]$r.stage]))
        }
    }
    [void]$lines.Add('')
    [void]$lines.Add('The drawer is FileUse\Discharged. Nothing was deleted.')
    $rosterPath = Join-Path $DockPath '00 ROSTER.txt'
    Set-Content -LiteralPath $rosterPath -Value $lines -Encoding UTF8
}

function Sync-TenureDock {
    Initialize-TenureSchema
    foreach ($dir in @($DockPath, $DischargedPath)) {
        if (-not (Test-Path -LiteralPath $dir)) {
            New-Item -ItemType Directory -Path $dir -Force | Out-Null
        }
    }
    Write-BenchDesktopIni -Folder $DockPath -DisplayName 'The Bench' -Tip 'Finite hooks. Tenure applies. The wall cabinet is the tree.'
    Write-BenchDesktopIni -Folder $DischargedPath -DisplayName 'Discharged' -Tip 'Honorable discharge. Off the bench, still in the shop.'

    $now = Format-Dts (Get-Date)
    $rows = @(Invoke-TSql -Query @'
SELECT t.path, t.stage, t.window_end, t.last_click, f.open_count,
       ROUND((JULIANDAY(t.window_end) - JULIANDAY(@n)) * 24, 1) AS hours_left
FROM tenure t
LEFT JOIN files f ON f.path = t.path
WHERE t.retired_at IS NULL AND t.window_end >= @n
ORDER BY t.stage DESC, t.last_click DESC
LIMIT @top;
'@ -SqlParameters @{ n = $now; top = $Top })

    $discharged = @(Invoke-TSql -Query @'
SELECT path, stage, retired_at, window_end
FROM tenure
WHERE retired_at IS NOT NULL
ORDER BY retired_at DESC
LIMIT 80;
'@)

    $dischargedToday = @(Invoke-TSql -Query @'
SELECT path, stage, retired_at
FROM tenure
WHERE retired_at IS NOT NULL AND retired_at >= @d
ORDER BY retired_at DESC;
'@ -SqlParameters @{ d = (Get-Date).ToString('yyyy-MM-dd') + ' 00:00:00' })

    $wantedBench = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)
    [void]$wantedBench.Add('00 ROSTER.txt')
    [void]$wantedBench.Add('desktop.ini')
    $wantedDisc = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)
    [void]$wantedDisc.Add('desktop.ini')

    $sh = New-Object -ComObject WScript.Shell
    $written = 0

    foreach ($row in $rows) {
        $target = [string]$row.path
        if (-not $target) { continue }
        if (-not (Test-Path -LiteralPath $target)) { continue }

        $hours = 0.0
        [void][double]::TryParse([string]$row.hours_left, [ref]$hours)
        $days = [math]::Max(0, [int][math]::Round($hours / 24.0))
        $stage = [int]$row.stage
        $base = Get-SafeBaseName $target
        $tag = Get-PathTag $target
        $name = 'S{0} {1}d — {2} [{3}].lnk' -f $stage, $days, $base, $tag
        $lnkPath = Join-Path $DockPath $name
        [void]$wantedBench.Add($name)

        $comment = '{0} · {1}d left · last seen {2}' -f $script:StageName[$stage], $days, $row.last_click
        Write-ToolShortcut -Shell $sh -LnkPath $lnkPath -Target $target -Comment $comment
        $written++
    }

    foreach ($row in $discharged) {
        $target = [string]$row.path
        if (-not $target) { continue }
        if (-not (Test-Path -LiteralPath $target)) { continue }
        $base = Get-SafeBaseName $target
        $tag = Get-PathTag $target
        $when = ([string]$row.retired_at) -replace '[: ]', '-'
        $name = '{0} — {1} [{2}].lnk' -f $when.Substring(0, [math]::Min(16, $when.Length)), $base, $tag
        $lnkPath = Join-Path $DischargedPath $name
        [void]$wantedDisc.Add($name)
        $comment = 'Thank you for your service. Was {0}. Discharged {1}.' -f $script:StageName[[int]$row.stage], $row.retired_at
        Write-ToolShortcut -Shell $sh -LnkPath $lnkPath -Target $target -Comment $comment
    }

    Get-ChildItem -LiteralPath $DockPath -Filter '*.lnk' -File -ErrorAction SilentlyContinue |
        Where-Object { -not $wantedBench.Contains($_.Name) } |
        Remove-Item -Force
    Get-ChildItem -LiteralPath $DischargedPath -Filter '*.lnk' -File -ErrorAction SilentlyContinue |
        Where-Object { -not $wantedDisc.Contains($_.Name) } |
        Remove-Item -Force

    [void][Runtime.InteropServices.Marshal]::ReleaseComObject($sh)
    Write-BenchRoster -OnDuty $rows -DischargedToday $dischargedToday
    Write-Host "Bench has $written tool(s). Drawer has $($discharged.Count) discharge(s)."
}

function Install-BenchStartup {
    if (-not (Test-Path -LiteralPath $DockPath)) {
        New-Item -ItemType Directory -Path $DockPath -Force | Out-Null
    }
    if (-not (Test-Path -LiteralPath $startupDir)) {
        New-Item -ItemType Directory -Path $startupDir -Force | Out-Null
    }
    $sh = New-Object -ComObject WScript.Shell
    $sc = $sh.CreateShortcut($startupLnk)
    $sc.TargetPath = "$env:WINDIR\explorer.exe"
    $sc.Arguments  = "`"$DockPath`""
    $sc.WorkingDirectory = $DockPath
    $sc.WindowStyle = 1
    $sc.Description = 'Open The Bench at logon'
    $sc.Save()
    [void][Runtime.InteropServices.Marshal]::ReleaseComObject($sc)
    [void][Runtime.InteropServices.Marshal]::ReleaseComObject($sh)
    Write-Host "Startup shortcut written: $startupLnk"
    Write-Host 'Open The Bench once, set Extra Large Icons. Windows remembers the view.'
}

function Uninstall-BenchStartup {
    if (Test-Path -LiteralPath $startupLnk) {
        Remove-Item -LiteralPath $startupLnk -Force
        Write-Host "Removed $startupLnk"
    } else {
        Write-Host 'No startup shortcut to remove.'
    }
}

# ---------------------------------------------------------------------------
$did = $false
if ($Apply -or $Retire -or $Roster -or $History -or $SyncDock) {
    Initialize-TenureSchema
}
if ($Apply)            { Invoke-TenureApply; $did = $true }
if ($Retire)           { Invoke-TenureRetire; $did = $true }
if ($SyncDock)         { Sync-TenureDock; $did = $true }
if ($InstallStartup)   { Install-BenchStartup; $did = $true }
if ($UninstallStartup) { Uninstall-BenchStartup; $did = $true }
if ($Open)             { Start-Process explorer.exe -ArgumentList "`"$DockPath`""; $did = $true }
if ($Roster)           { Get-TenureRoster | Format-Table -AutoSize; $did = $true }
if ($History)          { Get-TenureHistory | Format-Table -AutoSize; $did = $true }

if (-not $did) {
    Write-Host @"
The Bench — finite hooks, tenure applies

  .\FileUse.ps1 -Harvest
  .\FileUse-Tenure.ps1 -Apply -Retire -SyncDock
  .\FileUse-Tenure.ps1 -InstallStartup
  .\FileUse-Tenure.ps1 -Open
  .\FileUse-Tenure.ps1 -Roster
  .\FileUse-Tenure.ps1 -History

Bench:      $DockPath
Drawer:     $DischargedPath
Ladder:     48h -> 5d -> 14d -> 30d (renew)
Same-day extra clicks do not promote (default $($MinHoursBetweenPromote)h gap).
"@
}