import { Database } from "bun:sqlite";
import { dirname, join } from "node:path";
import { mkdirSync } from "node:fs";

export type Direction = "out" | "in";
export type Priority = "fyi" | "needs-answer";
export type Message = { id:string; direction:Direction; sessionId:string; ticketId:string|null; channel:string; chatId:number; priority:Priority; parentId:string|null; text:string; createdAt:number; attempts:number; wakeAttempts?:number; deliveredAt:number|null; escalatedAt:number|null; tgMessageId:number|null };
export type MessageAttachment={messageId:string;seq:number;path:string;bytes:number;tgMessageId:number};
export interface Store { mintId():string; insertMessage(m:Message):void; getMessage(id:string):Message|null; getByTgMessageId(chatId:number,tgMessageId:number):Message|null; undelivered(sessionId:string):Message[]; markAttempt(id:string):number; markWakeAttempt(id:string):number; markDelivered(id:string,at:number):void; markEscalated(id:string,at:number):void; staleUndelivered(olderThan:number):Message[]; recentOutbound(sessionId:string,since:number):Message|null; getMark(sessionId:string):{emoji:string;lastSeenAt:number}|null; setMark(sessionId:string,emoji:string,now:number):void; touchMark(sessionId:string,now:number):void; cursor(channel:string):number; setCursor(channel:string,date:number):void; pruneOlderThan(cutoff:number):number; close():void; listMarks():Array<{sessionId:string;emoji:string;lastSeenAt:number}>; updateText(id:string,text:string):void; setTgMessageId(id:string,tgMessageId:number):void; recordAttachment(messageId:string,seq:number,path:string,bytes:number,tgMessageId:number):void; listAttachments(messageId:string):MessageAttachment[]; claimMain(sessionId:string,now:number):void; getMainClaim():{sessionId:string;claimedAt:number}|null; recordPane(sessionId:string,socket:string,pane:string,panePid:number,now:number):void; getPane(sessionId:string):{socket:string;pane:string;panePid:number}|null; lastWake(sessionId:string):number; markWake(sessionId:string,at:number):void }
const alphabet="0123456789abcdefghjkmnpqrstvwxyz";
const row=(r:any):Message|null=>r?({id:r.id,direction:r.direction,sessionId:r.session_id,ticketId:r.ticket_id,channel:r.channel,chatId:r.chat_id,priority:r.priority,parentId:r.parent_id,text:r.text,createdAt:r.created_at,attempts:r.attempts,wakeAttempts:r.wake_attempts,deliveredAt:r.delivered_at,escalatedAt:r.escalated_at,tgMessageId:r.tg_message_id}):null;
export function openStore(path=join(process.env.HOME??"", ".local/state/overdeck/botmaster/messages.db")):Store { mkdirSync(dirname(path),{recursive:true,mode:0o700}); const db=new Database(path); db.exec(`PRAGMA journal_mode=WAL; CREATE TABLE IF NOT EXISTS messages (id TEXT PRIMARY KEY,direction TEXT,session_id TEXT,ticket_id TEXT,channel TEXT,chat_id INTEGER,priority TEXT,parent_id TEXT,text TEXT,created_at INTEGER,attempts INTEGER DEFAULT 0,wake_attempts INTEGER DEFAULT 0,delivered_at INTEGER,escalated_at INTEGER,tg_message_id INTEGER); CREATE TABLE IF NOT EXISTS message_attachments (message_id TEXT,seq INTEGER,path TEXT,bytes INTEGER,tg_message_id INTEGER,PRIMARY KEY (message_id,seq)); CREATE TABLE IF NOT EXISTS inbox_cursor (channel TEXT PRIMARY KEY,last_chat_message_date INTEGER); CREATE TABLE IF NOT EXISTS session_identity (session_id TEXT PRIMARY KEY,emoji TEXT,assigned_at INTEGER,last_seen_at INTEGER); CREATE TABLE IF NOT EXISTS main_claim (id INTEGER PRIMARY KEY CHECK(id=1),session_id TEXT,claimed_at INTEGER); CREATE TABLE IF NOT EXISTS session_pane (session_id TEXT PRIMARY KEY,socket TEXT,pane TEXT,pane_pid INTEGER,recorded_at INTEGER,last_wake_at INTEGER DEFAULT 0);`); try{db.exec("ALTER TABLE messages ADD COLUMN wake_attempts INTEGER DEFAULT 0")}catch{}
 const one=(sql:string,...p:any[])=>row(db.query(sql).get(...p)); const many=(sql:string,...p:any[])=>db.query(sql).all(...p).map(row) as Message[];
 return { mintId(){let s="";for(let i=0;i<5;i++)s+=alphabet[Math.floor(Math.random()*alphabet.length)];return s}, insertMessage(m){for(let attempt=0;attempt<8;attempt++){try{db.query(`INSERT INTO messages (id,direction,session_id,ticket_id,channel,chat_id,priority,parent_id,text,created_at,attempts,wake_attempts,delivered_at,escalated_at,tg_message_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`).run(m.id,m.direction,m.sessionId,m.ticketId,m.channel,m.chatId,m.priority,m.parentId,m.text,m.createdAt,m.attempts,m.wakeAttempts??0,m.deliveredAt,m.escalatedAt,m.tgMessageId);return}catch(error){
    // Only an id collision is retryable; anything else (schema drift, constraint,
    // disk) must surface verbatim instead of masquerading as "out of ids".
    if(!/UNIQUE constraint/i.test(String((error as Error)?.message??error)))throw error;
    if(attempt===7)throw error;m.id=this.mintId()}}},getMessage:id=>one("SELECT * FROM messages WHERE id=?",id),getByTgMessageId:(c,t)=>one("SELECT * FROM messages WHERE chat_id=? AND tg_message_id=?",c,t),undelivered:s=>many("SELECT * FROM messages WHERE direction='in' AND session_id=? AND delivered_at IS NULL AND escalated_at IS NULL ORDER BY created_at",s),markAttempt(id){db.query("UPDATE messages SET attempts=attempts+1 WHERE id=?").run(id);return Number(db.query("SELECT attempts FROM messages WHERE id=?").get(id)?.attempts??0)},markWakeAttempt(id){db.query("UPDATE messages SET wake_attempts=wake_attempts+1 WHERE id=?").run(id);return Number(db.query("SELECT wake_attempts FROM messages WHERE id=?").get(id)?.wake_attempts??0)},markDelivered:(id,a)=>{db.query("UPDATE messages SET delivered_at=? WHERE id=?").run(a,id)},markEscalated:(id,a)=>{db.query("UPDATE messages SET escalated_at=? WHERE id=? AND escalated_at IS NULL").run(a,id)},staleUndelivered:o=>many("SELECT * FROM messages WHERE direction='in' AND delivered_at IS NULL AND escalated_at IS NULL AND created_at<? ORDER BY created_at",o),recentOutbound:(s,since)=>one("SELECT * FROM messages WHERE direction='out' AND session_id=? AND created_at>? ORDER BY created_at DESC LIMIT 1",s,since),getMark(s){const r:any=db.query("SELECT emoji,last_seen_at FROM session_identity WHERE session_id=?").get(s);return r?{emoji:r.emoji,lastSeenAt:r.last_seen_at}:null},setMark(s,e,n){db.query("INSERT INTO session_identity VALUES (?,?,?,?) ON CONFLICT(session_id) DO UPDATE SET emoji=excluded.emoji,last_seen_at=excluded.last_seen_at").run(s,e,n,n)},touchMark:(s,n)=>{db.query("UPDATE session_identity SET last_seen_at=? WHERE session_id=?").run(n,s)},cursor:c=>Number(db.query("SELECT last_chat_message_date FROM inbox_cursor WHERE channel=?").get(c)?.last_chat_message_date??0),setCursor:(c,d)=>{db.query("INSERT INTO inbox_cursor VALUES (?,?) ON CONFLICT(channel) DO UPDATE SET last_chat_message_date=excluded.last_chat_message_date").run(c,d)},pruneOlderThan(c){return Number(db.query("DELETE FROM messages WHERE created_at<?").run(c).changes)},listMarks(){return db.query("SELECT session_id,emoji,last_seen_at FROM session_identity").all().map((r:any)=>({sessionId:r.session_id,emoji:r.emoji,lastSeenAt:r.last_seen_at}))},updateText:(id,t)=>{db.query("UPDATE messages SET text=? WHERE id=?").run(t,id)},setTgMessageId:(id,t)=>{db.query("UPDATE messages SET tg_message_id=? WHERE id=?").run(t,id)},recordAttachment(messageId,seq,path,bytes,tgMessageId){db.query("INSERT INTO message_attachments (message_id,seq,path,bytes,tg_message_id) VALUES (?,?,?,?,?)").run(messageId,seq,path,bytes,tgMessageId)},listAttachments(messageId){return db.query("SELECT message_id,seq,path,bytes,tg_message_id FROM message_attachments WHERE message_id=? ORDER BY seq").all(messageId).map((r:any)=>({messageId:r.message_id,seq:r.seq,path:r.path,bytes:r.bytes,tgMessageId:r.tg_message_id}))},recordPane(s,socket,pane,panePid,n){db.query("INSERT INTO session_pane (session_id,socket,pane,pane_pid,recorded_at) VALUES (?,?,?,?,?) ON CONFLICT(session_id) DO UPDATE SET socket=excluded.socket,pane=excluded.pane,pane_pid=excluded.pane_pid,recorded_at=excluded.recorded_at").run(s,socket,pane,panePid,n)},getPane(s){const r:any=db.query("SELECT socket,pane,pane_pid FROM session_pane WHERE session_id=?").get(s);return r?{socket:r.socket,pane:r.pane,panePid:r.pane_pid}:null},lastWake(s){return Number(db.query("SELECT last_wake_at FROM session_pane WHERE session_id=?").get(s)?.last_wake_at??0)},markWake(s,at){db.query("UPDATE session_pane SET last_wake_at=? WHERE session_id=?").run(at,s)},claimMain(s,n){db.query("INSERT INTO main_claim VALUES (1,?,?) ON CONFLICT(id) DO UPDATE SET session_id=excluded.session_id, claimed_at=excluded.claimed_at").run(s,n)},getMainClaim(){const r:any=db.query("SELECT session_id,claimed_at FROM main_claim WHERE id=1").get();return r?{sessionId:r.session_id,claimedAt:r.claimed_at}:null},close(){db.close()} };
}