import { sql, type SQLWrapper } from 'drizzle-orm'
import { authorizeContentAction, type ContentPrincipal } from './authz.js'
import { canonicalContentMigrationHash } from './migrations/content-model.js'
import { normalizeContentTypeDefinition, type ContentTypeDefinition, type ResolvedContentType } from './registry.js'
import { assertActiveContentTransaction, type ContentSchemaValue, type ContentTransaction } from './schema.js'
import { normalizeContentStatusDefinition, type ContentStatusDefinition, type ResolvedContentStatus } from './status.js'
import type { ContentExportBundle, ContentImportLimits } from './import.js'

export interface ContentExportOverride { readonly reason:string; readonly operationId:string }
export interface ContentExportAudit { recordOverride(tx:ContentTransaction,principal:ContentPrincipal,typeKeys:readonly string[],override:ContentExportOverride):Promise<void> }
export type ContentExportErrorCode='validation'|'not-found'|'forbidden'|'integrity'|'limit'
export class ContentExportError extends Error { override readonly name='ContentExportError'; constructor(readonly code:ContentExportErrorCode,readonly detail:string,readonly field?:string){super(`content export ${code}: ${detail}${field?` (${field})`:''}`)} }
export function isContentExportError(error:unknown):error is ContentExportError{return error instanceof ContentExportError}
const fail=(code:ContentExportErrorCode,detail:string,field?:string):never=>{throw new ContentExportError(code,detail,field)}

const TYPE_KEY=/^[a-z][a-z0-9_-]*$/
function bounded(value:unknown,field:string,max:number):string{if(typeof value!=='string')fail('validation','must be a string',field);const normalized=(value as string).trim();if(!normalized||normalized.length>max)fail('validation',`must contain 1..${max} characters`,field);return normalized}
function typeKey(value:unknown,field:string):string{const normalized=bounded(value,field,128);if(!TYPE_KEY.test(normalized))fail('validation','must be a normalized content type key',field);return normalized}
function positive(value:unknown,field:string):number{const number=Number(value);if(!Number.isSafeInteger(number)||number<1)fail('integrity','must be a positive safe integer',field);return number}
function bool(value:unknown):boolean{return value===true||value===1||value==='1'}
function iso(value:unknown,field:string):string{const date=value instanceof Date?value:new Date(String(value));if(Number.isNaN(date.getTime()))fail('integrity','must be a timestamp',field);return date.toISOString()}
function parseJson(value:unknown,field:string):unknown{if(typeof value!=='string')return value;try{return JSON.parse(value) as unknown}catch{fail('integrity','contains invalid JSON',field)}}
function object(value:unknown,field:string):Record<string,unknown>{const parsed=parseJson(value,field);if(typeof parsed!=='object'||parsed===null||Array.isArray(parsed))fail('integrity','must be an object',field);return parsed as Record<string,unknown>}
const FORBIDDEN=/(?:password|credential)/i
function storage(value:unknown,field='value',depth=0):ContentSchemaValue{if(depth>32)fail('limit','exceeds maximum nested depth',field);if(value===null||typeof value==='string'||typeof value==='boolean')return value;if(value instanceof Date)return value.toISOString();if(typeof value==='number'){if(!Number.isFinite(value))fail('integrity','contains non-finite number',field);return value}if(Array.isArray(value))return Object.freeze(value.map((item,index)=>storage(item,`${field}.${index}`,depth+1)));if(typeof value==='object'){const output:Record<string,ContentSchemaValue>={};for(const [key,child] of Object.entries(value as Record<string,unknown>)){if(FORBIDDEN.test(key))fail('integrity','contains forbidden password/credential material',`${field}.${key}`);if(child!==undefined)output[key]=storage(child,`${field}.${key}`,depth+1)}return Object.freeze(output)}return fail('integrity','contains unsupported value',field)}
function rowsOf<Row extends Record<string,unknown>>(value:unknown):readonly Row[]{if(Array.isArray(value))return value as Row[];const rows=(value as {rows?:unknown})?.rows;if(Array.isArray(rows))return rows as Row[];return fail('integrity','database adapter returned invalid rows')}
async function execute<Row extends Record<string,unknown>>(tx:ContentTransaction,query:SQLWrapper):Promise<readonly Row[]>{return rowsOf<Row>(await tx.execute(query))}
function sqlList(values:readonly string[]):SQLWrapper{return sql.join(values.map((value)=>sql`${value}`),sql`, `)}
function validateLimits(limits:ContentImportLimits):void{for(const [key,value] of Object.entries(limits))if(!Number.isSafeInteger(value)||value<1)fail('validation','must be a positive safe integer',`limits.${key}`);if(limits.batchSize>1000)fail('validation','batchSize must be <= 1000','limits.batchSize')}
function origin(value:unknown,field:string):'code'|'db'|'import'{if(value==='code'||value==='db'||value==='import')return value;return fail('integrity','invalid definition origin',field)}

interface CurrentDefinitionRow extends Record<string,unknown>{key:string;origin:string;version:number|string;active:boolean|number|string;currentRevision:number|string;canonicalHash:string;definition:unknown;shadowedDbVersion:number|string|null}
interface VersionRow extends Record<string,unknown>{kind:'type'|'status';key:string;revision:number|string;canonicalHash:string;definition:unknown;origin:string;createdAt:Date|string}
interface EntryRow extends Record<string,unknown>{id:string;slug:string;type:string;title:string;body:string;status:string;visibility:'public'|'private'|'members';publishedAt:Date|string|null;author:string;createdAt:Date|string;updatedAt:Date|string;parentId:string|null;menuOrder:number|string;templateKey:string|null;excerpt:string;featuredMedia:unknown;commentStatus:'open'|'closed';pingStatus:'open'|'closed';sticky:boolean|number|string;format:string|null;deletedAt:Date|string|null;lastEditedBy:string;typeDefinitionRevision:number|string;statusDefinitionRevision:number|string;passwordProtected:boolean|number|string}
interface AssignmentRow extends Record<string,unknown>{entryId:string;termId:string}
interface TermRow extends Record<string,unknown>{id:string;taxonomy:string;slug:string;name:string;parentId:string|null;depth:number|string;createdAt:Date|string}
interface RevisionRow extends Record<string,unknown>{id:string;entryId:string;seq:number|string;title:string;body:string;slug:string;type:string;termIds:unknown;snapshot:unknown|null;editor:string;createdAt:Date|string}

function resolvedType(row:CurrentDefinitionRow,version?:VersionRow):ResolvedContentType{const definition=normalizeContentTypeDefinition((version?parseJson(version.definition,'type.version.definition'):parseJson(row.definition,'type.definition')) as ContentTypeDefinition);return Object.freeze({...definition,active:bool(row.active),origin:(version?origin(version.origin,'type.version.origin'):origin(row.origin,'type.origin'))==='code'?'code':'db',version:positive(row.version,'type.version'),revision:version?positive(version.revision,'type.revision'):positive(row.currentRevision,'type.currentRevision'),canonicalHash:String(version?.canonicalHash??row.canonicalHash),...(row.shadowedDbVersion===null?{}:{shadowedDbVersion:positive(row.shadowedDbVersion,'type.shadowedDbVersion')})})}
function resolvedStatus(row:CurrentDefinitionRow,version?:VersionRow):ResolvedContentStatus{const definition=normalizeContentStatusDefinition((version?parseJson(version.definition,'status.version.definition'):parseJson(row.definition,'status.definition')) as ContentStatusDefinition);return Object.freeze({...definition,active:bool(row.active),origin:(version?origin(version.origin,'status.version.origin'):origin(row.origin,'status.origin'))==='code'?'code':'db',version:positive(row.version,'status.version'),revision:version?positive(version.revision,'status.revision'):positive(row.currentRevision,'status.currentRevision'),canonicalHash:String(version?.canonicalHash??row.canonicalHash),...(row.shadowedDbVersion===null?{}:{shadowedDbVersion:positive(row.shadowedDbVersion,'status.shadowedDbVersion')})})}
function definitionBundle(row:CurrentDefinitionRow,versions:readonly VersionRow[]):ContentSchemaValue{const currentDefinition=parseJson(row.definition,'definition.current'),items=[...versions].sort((a:VersionRow,b:VersionRow)=>Number(a.revision)-Number(b.revision)).map((version:VersionRow)=>storage({revision:positive(version.revision,'definition.version.revision'),canonicalHash:String(version.canonicalHash),definition:parseJson(version.definition,'definition.version.definition'),origin:origin(version.origin,'definition.version.origin'),createdAt:iso(version.createdAt,'definition.version.createdAt')},'definition.version'));return storage({key:String(row.key),origin:origin(row.origin,'definition.origin'),version:positive(row.version,'definition.version'),revision:positive(row.currentRevision,'definition.currentRevision'),active:bool(row.active),canonicalHash:String(row.canonicalHash),definition:currentDefinition,versions:items},'definition')}
function stringArray(value:unknown,field:string):readonly string[]{const parsed=parseJson(value,field);if(!Array.isArray(parsed))throw new ContentExportError('integrity','must be an array',field);const values=parsed as unknown[];return Object.freeze(values.map((item:unknown,index:number)=>bounded(item,`${field}.${index}`,128)))}

export async function exportContent(tx:ContentTransaction,principal:ContentPrincipal,input:{typeKeys:readonly string[];includeRevisions:boolean;limits:ContentImportLimits;overrideCanExport?:ContentExportOverride},deps:{audit:ContentExportAudit}):Promise<ContentExportBundle>{
  assertActiveContentTransaction(tx);validateLimits(input.limits);if(typeof input.includeRevisions!=='boolean')fail('validation','must be boolean','includeRevisions')
  const requested=[...new Set(input.typeKeys.map((value,index)=>typeKey(value,`typeKeys.${index}`)))].sort();if(requested.length===0||requested.length>input.limits.maxDefinitions)fail('limit','typeKeys exceed export definition limit','typeKeys')
  const typeRows=await execute<CurrentDefinitionRow>(tx,sql`SELECT key,origin,version,active,current_revision AS "currentRevision",canonical_hash AS "canonicalHash",definition,shadowed_db_version AS "shadowedDbVersion" FROM content_type_definitions WHERE key IN (${sqlList(requested)}) ORDER BY key`)
  if(typeRows.length!==requested.length){const found=new Set(typeRows.map((row)=>row.key)),missing=requested.find((key)=>!found.has(key));fail('not-found',`content type ${missing??'unknown'} is unavailable`,'typeKeys')}
  const currentTypes=new Map(typeRows.map((row)=>[row.key,resolvedType(row)])),overridden:string[]=[]
  for(const key of requested){const type=currentTypes.get(key)!;authorizeContentAction({principal,type,operation:'read'});if(type.canExport)continue;if(!input.overrideCanExport)fail('forbidden',`content type ${key} does not allow export`);authorizeContentAction({principal,type,operation:'manageType'});overridden.push(key)}
  let override:ContentExportOverride|undefined
  if(overridden.length){const reason=bounded(input.overrideCanExport!.reason,'overrideCanExport.reason',512),operationId=bounded(input.overrideCanExport!.operationId,'overrideCanExport.operationId',256);override=Object.freeze({reason,operationId})}

  const entryRows=await execute<EntryRow>(tx,sql`SELECT id,slug,type,title,body,status,visibility,published_at AS "publishedAt",author,created_at AS "createdAt",updated_at AS "updatedAt",parent_id AS "parentId",menu_order AS "menuOrder",template_key AS "templateKey",excerpt,featured_media AS "featuredMedia",comment_status AS "commentStatus",ping_status AS "pingStatus",sticky,format,deleted_at AS "deletedAt",last_edited_by AS "lastEditedBy",type_definition_revision AS "typeDefinitionRevision",status_definition_revision AS "statusDefinitionRevision",EXISTS (SELECT 1 FROM content_password_credentials c WHERE c.entry_id=content_entries.id) AS "passwordProtected" FROM content_entries WHERE type IN (${sqlList(requested)}) ORDER BY id`)
  if(entryRows.length>input.limits.maxEntries)fail('limit','entries exceed export maxEntries')
  const typeVersionRows=await execute<VersionRow>(tx,sql`SELECT definition_kind AS kind,definition_key AS key,revision,canonical_hash AS "canonicalHash",definition,origin,created_at AS "createdAt" FROM content_definition_versions WHERE definition_kind='type' AND definition_key IN (${sqlList(requested)}) ORDER BY definition_key,revision`)
  const typeVersionMap=new Map(typeVersionRows.map((row)=>[`type:${row.key}:${Number(row.revision)}`,row]))
  for(const row of typeRows)if(!typeVersionMap.has(`type:${row.key}:${Number(row.currentRevision)}`))fail('integrity',`current type ${row.key} has no immutable revision`)
  const referencedTypeDefinitions=[...typeRows.map((row)=>normalizeContentTypeDefinition(parseJson(row.definition,'type.definition') as ContentTypeDefinition)),...typeVersionRows.map((row)=>normalizeContentTypeDefinition(parseJson(row.definition,'type.version.definition') as ContentTypeDefinition))]
  const explicitStatusKeys=referencedTypeDefinitions.flatMap((definition)=>definition.statusKeys??[]),statusKeys=[...new Set([...explicitStatusKeys,...entryRows.map((row)=>row.status)])].sort()
  const taxonomyKeys=[...new Set(referencedTypeDefinitions.flatMap((definition)=>definition.taxonomies))].sort()
  const statusRows=statusKeys.length?await execute<CurrentDefinitionRow>(tx,sql`SELECT key,origin,version,active,current_revision AS "currentRevision",canonical_hash AS "canonicalHash",definition,shadowed_db_version AS "shadowedDbVersion" FROM content_status_definitions WHERE key IN (${sqlList(statusKeys)}) ORDER BY key`):[]
  if(statusRows.length!==statusKeys.length)fail('integrity','selected corpus references an unavailable status')
  const currentStatuses=new Map(statusRows.map((row)=>[row.key,row]))
  const statusVersionRows=statusKeys.length?await execute<VersionRow>(tx,sql`SELECT definition_kind AS kind,definition_key AS key,revision,canonical_hash AS "canonicalHash",definition,origin,created_at AS "createdAt" FROM content_definition_versions WHERE definition_kind='status' AND definition_key IN (${sqlList(statusKeys)}) ORDER BY definition_key,revision`):[]
  const versionRows=Object.freeze([...typeVersionRows,...statusVersionRows]),versionMap=new Map(versionRows.map((row)=>[`${row.kind}:${row.key}:${Number(row.revision)}`,row]))
  for(const row of statusRows)if(!versionMap.has(`status:${row.key}:${Number(row.currentRevision)}`))fail('integrity',`current status ${row.key} has no immutable revision`)

  for(const entry of entryRows){const typeRow=typeRows.find((row)=>row.key===entry.type),statusRow=currentStatuses.get(entry.status),typeVersion=versionMap.get(`type:${entry.type}:${Number(entry.typeDefinitionRevision)}`),statusVersion=versionMap.get(`status:${entry.status}:${Number(entry.statusDefinitionRevision)}`);if(!typeRow||!statusRow||!typeVersion||!statusVersion)throw new ContentExportError('integrity',`entry ${entry.id} has unresolved definition pins`);const type=resolvedType(typeRow,typeVersion),status=resolvedStatus(statusRow,statusVersion);authorizeContentAction({principal,type,operation:bool(entry.passwordProtected)?'readProtected':'read',entry:{id:entry.id,author:entry.author,status:entry.status,visibility:entry.visibility},status})}

  const entryIds=entryRows.map((row)=>row.id),assignments=entryIds.length?await execute<AssignmentRow>(tx,sql`SELECT entry_id AS "entryId",term_id AS "termId" FROM content_entry_terms WHERE entry_id IN (${sqlList(entryIds)}) ORDER BY entry_id,term_id`):[]
  const assignmentIds=[...new Set(assignments.map((row)=>row.termId))]
  const termRows=taxonomyKeys.length?await execute<TermRow>(tx,sql`SELECT id,taxonomy,slug,name,parent_id AS "parentId",depth,created_at AS "createdAt" FROM content_terms WHERE taxonomy IN (${sqlList(taxonomyKeys)}) ORDER BY taxonomy,depth,id`):[]
  if(termRows.length>Math.max(input.limits.maxEntries*10,input.limits.maxDefinitions))fail('limit','taxonomy terms exceed export limit')
  const termIds=new Set(termRows.map((row)=>row.id));for(const id of assignmentIds)if(!termIds.has(id))fail('integrity',`entry assignment references term ${id} outside selected type taxonomies`)
  const terms=Object.freeze(termRows.map((row)=>storage({id:row.id,taxonomy:row.taxonomy,slug:row.slug,name:row.name,parentId:row.parentId,depth:Number(row.depth),createdAt:iso(row.createdAt,'term.createdAt')},'term')))
  const taxonomies=Object.freeze(taxonomyKeys.map((key)=>storage({key,version:'1'},'taxonomy')))
  const assignmentMap=new Map<string,string[]>();for(const row of assignments){const values=assignmentMap.get(row.entryId)??[];values.push(row.termId);assignmentMap.set(row.entryId,values)}
  const entries=Object.freeze(entryRows.map((row)=>storage({id:row.id,slug:row.slug,type:row.type,title:row.title,body:row.body,status:row.status,visibility:row.visibility,publishedAt:row.publishedAt===null?null:iso(row.publishedAt,'entry.publishedAt'),author:row.author,createdAt:iso(row.createdAt,'entry.createdAt'),updatedAt:iso(row.updatedAt,'entry.updatedAt'),parentId:row.parentId,menuOrder:Number(row.menuOrder),templateKey:row.templateKey,excerpt:row.excerpt,featuredMedia:row.featuredMedia===null?null:storage(parseJson(row.featuredMedia,'entry.featuredMedia'),'entry.featuredMedia'),commentStatus:row.commentStatus,pingStatus:row.pingStatus,sticky:bool(row.sticky),format:row.format,deletedAt:row.deletedAt===null?null:iso(row.deletedAt,'entry.deletedAt'),lastEditedBy:row.lastEditedBy,typeDefinitionRevision:Number(row.typeDefinitionRevision),statusDefinitionRevision:Number(row.statusDefinitionRevision),termIds:Object.freeze(assignmentMap.get(row.id)??[])},'entry')))

  const revisions:ContentSchemaValue[]=[]
  if(input.includeRevisions&&entryIds.length){const rows=await execute<RevisionRow>(tx,sql`SELECT id,entry_id AS "entryId",seq,title,body,slug,type,term_ids AS "termIds",snapshot,editor,created_at AS "createdAt" FROM content_revisions WHERE entry_id IN (${sqlList(entryIds)}) ORDER BY entry_id,seq,id`);if(rows.length>input.limits.maxRevisions)fail('limit','revisions exceed export maxRevisions');for(const row of rows)revisions.push(storage({id:row.id,entryId:row.entryId,seq:Number(row.seq),title:row.title,body:row.body,slug:row.slug,type:row.type,termIds:stringArray(row.termIds,'revision.termIds'),...(row.snapshot===null?{}:{snapshot:storage(parseJson(row.snapshot,'revision.snapshot'),'revision.snapshot')}),editor:row.editor,createdAt:iso(row.createdAt,'revision.createdAt')},'revision'))}

  const types=Object.freeze(typeRows.map((row)=>definitionBundle(row,versionRows.filter((version)=>version.kind==='type'&&version.key===row.key))))
  const statuses=Object.freeze(statusRows.map((row)=>definitionBundle(row,versionRows.filter((version)=>version.kind==='status'&&version.key===row.key))))
  if(types.length+statuses.length+taxonomies.length>input.limits.maxDefinitions)fail('limit','export definitions exceed maxDefinitions')
  const bundle:ContentExportBundle=Object.freeze({version:1,generatedAt:new Date().toISOString(),types,statuses,taxonomies,terms,entries,revisions:Object.freeze(revisions)})
  const bytes=new TextEncoder().encode(JSON.stringify(bundle));if(bytes.byteLength>input.limits.maxBytes)fail('limit','export bundle exceeds maxBytes')
  if(override)await deps.audit.recordOverride(tx,principal,Object.freeze(overridden.sort()),override)
  await canonicalContentMigrationHash(bundle)
  return bundle
}