export function createPostgresStore(pg, definitions, crypto) {
  const checkTable = name => { if (!Object.hasOwn(definitions,name)) throw new Error('TABLE_NOT_ALLOWED'); return name; };
  const checkField = (name,key) => { checkTable(name); if (!Object.hasOwn(definitions[name],key)) throw new Error('FIELD_NOT_ALLOWED'); return key; };
  const decode = (name,row) => {
    if (!row) return null;
    const out={};
    for (const [k,v] of Object.entries(row)) {
      if (k.startsWith('_') || k==='ACL' || !Object.hasOwn(definitions[name],k)) continue;
      const type=definitions[name][k];
      out[k]=v instanceof Date?v.toISOString():v;
      if(v!=null&&type.type==='Pointer')out[k]={__type:'Pointer',className:type.targetClass,objectId:v};
      if(v!=null&&type.type==='File')out[k]={__type:'File',name:v};
    }
    return out;
  };
  function encode(name,key,value){
    const {type}=definitions[name][checkField(name,key)];
    if(value===null)return{value:null,cast:''};
    if(type==='Pointer')return{value:value.objectId,cast:''};
    if(type==='File')return{value:value.name,cast:''};
    if(type==='Date')return{value:value instanceof Date?value.toISOString():(value?.iso||value),cast:'::timestamptz'};
    if(type==='Object'||type==='Array')return{value:JSON.stringify(value),cast:'::jsonb'};
    return{value,cast:''};
  }
  function make(connection){
    const store={
      async transaction(fn){return connection.tx(t=>fn(make(t)));},
      async lock(key){await connection.one('SELECT pg_advisory_xact_lock(hashtextextended($1,0))',[key]);},
      async get(name,id,{forUpdate=false}={}){
        checkTable(name);const row=await connection.oneOrNone('SELECT * FROM $1:name WHERE "objectId"=$2'+(forUpdate?' FOR UPDATE':''),[name,id]);return decode(name,row);
      },
      async list(name,{where={},afterId=null,limit=200,search=null,forUpdate=false}={}){
        checkTable(name);const values=[name],conditions=[];
        for(const[k,v]of Object.entries(where)){checkField(name,k);values.push(k);const key='$'+values.length+':name';
          if(v===null)conditions.push(key+' IS NULL');else{const e=encode(name,k,v);values.push(e.value);conditions.push(key+'=$'+values.length+e.cast);}}
        if(afterId){values.push(afterId);conditions.push('"objectId">$'+values.length);}
        if(search){const fields=['searchText','title','name','rawText','content','text'].filter(k=>definitions[name][k]?.type==='String');
          if(fields.length){values.push('%'+search.replace(/[\\%_]/g,'\\$&')+'%');const val='$'+values.length;conditions.push('('+fields.map(k=>{values.push(k);return'$'+values.length+':name ILIKE '+val;}).join(' OR ')+')');}}
        values.push(limit);const sql='SELECT * FROM $1:name'+(conditions.length?' WHERE '+conditions.join(' AND '):'')+' ORDER BY "objectId" LIMIT $'+values.length+(forUpdate?' FOR UPDATE':'');
        return(await connection.any(sql,values)).map(r=>decode(name,r));
      },
      async count(name,where={}){return(await store.list(name,{where,limit:10001})).length;},
      async create(name,data){
        checkTable(name);const now=new Date().toISOString();const item={...data,objectId:crypto.randomBytes(9).toString('base64url').slice(0,10),createdAt:now,updatedAt:now};
        const values=[name],columns=[],placeholders=[];
        for(const[k,v]of Object.entries(item)){if(v===undefined)continue;const e=encode(name,k,v);values.push(k);columns.push('$'+values.length+':name');values.push(e.value);placeholders.push('$'+values.length+e.cast);}
        // Objects are private by default; read and write access goes through authenticated functions.
        columns.push('"_rperm"','"_wperm"');placeholders.push("ARRAY[]::text[]","ARRAY[]::text[]");
        const row=await connection.one('INSERT INTO $1:name ('+columns.join(',')+') VALUES ('+placeholders.join(',')+') RETURNING *',values);return decode(name,row);
      },
      async update(name,id,changes,{expectedUpdatedAt}={}){
        checkTable(name);const data={...changes,updatedAt:new Date().toISOString()};const values=[name,id],sets=[];
        for(const[k,v]of Object.entries(data)){if(v===undefined)continue;const e=encode(name,k,v);values.push(k);const key='$'+values.length+':name';values.push(e.value);const value='$'+values.length+e.cast;sets.push(key+'='+(k==='updatedAt'?'GREATEST('+value+',"updatedAt" + interval \'1 millisecond\')':value));}
        let condition='"objectId"=$2';if(expectedUpdatedAt){values.push(expectedUpdatedAt);condition+=' AND "updatedAt"=$'+values.length+'::timestamptz';}
        const row=await connection.oneOrNone('UPDATE $1:name SET '+sets.join(',')+' WHERE '+condition+' RETURNING *',values);
        if(!row){const e=new Error('VERSION_CONFLICT');e.code='VERSION_CONFLICT';throw e;}return decode(name,row);
      },
      async remove(name,id){checkTable(name);await connection.none('DELETE FROM $1:name WHERE "objectId"=$2',[name,id]);},
    };return store;
  }
  return make(pg);
}