-- Audit Event Header Table
CREATE TABLE IF NOT EXISTS {{.AuditSchema}}.atevent (
  rid_atevent serial PRIMARY KEY,
  tablename text NOT NULL,
  tableprefix text,
  rid_parent integer NOT NULL,
  rid_deletedparent integer,
  changeuser text NOT NULL,
  changedate date NOT NULL,
  changetime time NOT NULL,
  actionx smallint NOT NULL,
  CONSTRAINT ck_atevent_action CHECK (actionx IN (1, 2, 3))
);

CREATE INDEX IF NOT EXISTS idx_atevent_tablename ON {{.AuditSchema}}.atevent(tablename);
CREATE INDEX IF NOT EXISTS idx_atevent_rid_parent ON {{.AuditSchema}}.atevent(rid_parent);
CREATE INDEX IF NOT EXISTS idx_atevent_changedate ON {{.AuditSchema}}.atevent(changedate);
CREATE INDEX IF NOT EXISTS idx_atevent_changeuser ON {{.AuditSchema}}.atevent(changeuser);

COMMENT ON TABLE {{.AuditSchema}}.atevent IS 'Audit trail header table - tracks all data changes';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.rid_atevent IS 'Audit event ID';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.tablename IS 'Name of the table that was modified';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.rid_parent IS 'Primary key value of the modified record';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.rid_deletedparent IS 'Parent reference for deleted records';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.changeuser IS 'User who made the change';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.changedate IS 'Date of change';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.changetime IS 'Time of change';
COMMENT ON COLUMN {{.AuditSchema}}.atevent.actionx IS 'Action type: 1=INSERT, 2=UPDATE, 3=DELETE';

-- Audit Event Detail Table
CREATE TABLE IF NOT EXISTS {{.AuditSchema}}.atdetail (
  rid_atdetail serial PRIMARY KEY,
  rid_atevent integer NOT NULL,
  datacolumn text NOT NULL,
  changedfrom text,
  changedto text,
  CONSTRAINT fk_atdetail_atevent FOREIGN KEY (rid_atevent)
    REFERENCES {{.AuditSchema}}.atevent(rid_atevent) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_atdetail_rid_atevent ON {{.AuditSchema}}.atdetail(rid_atevent);
CREATE INDEX IF NOT EXISTS idx_atdetail_datacolumn ON {{.AuditSchema}}.atdetail(datacolumn);

COMMENT ON TABLE {{.AuditSchema}}.atdetail IS 'Audit trail detail table - stores individual column changes';
COMMENT ON COLUMN {{.AuditSchema}}.atdetail.rid_atdetail IS 'Audit detail ID';
COMMENT ON COLUMN {{.AuditSchema}}.atdetail.rid_atevent IS 'Reference to audit event';
COMMENT ON COLUMN {{.AuditSchema}}.atdetail.datacolumn IS 'Name of the column that changed';
COMMENT ON COLUMN {{.AuditSchema}}.atdetail.changedfrom IS 'Old value before change';
COMMENT ON COLUMN {{.AuditSchema}}.atdetail.changedto IS 'New value after change';