-- Import into a NEW empty database via phpMyAdmin. Does not overwrite an existing installation.
CREATE TABLE church_ledger (
 id INT NOT NULL PRIMARY KEY,
 payload LONGTEXT NOT NULL,
 revision BIGINT NOT NULL DEFAULT 1,
 updated_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE church_ledger_history (
 revision BIGINT NOT NULL PRIMARY KEY,
 payload LONGTEXT NOT NULL,
 saved_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO church_ledger (id,payload,revision,updated_at) VALUES (1,'{"donations":[],"expenses":[]}',1,'2026-10-21T00:00:00Z');

-- APPLICATION V3 TABLES
CREATE TABLE IF NOT EXISTS church_users (
 id VARCHAR(100) PRIMARY KEY,email VARCHAR(254) NOT NULL UNIQUE,name VARCHAR(600) NOT NULL,
 role VARCHAR(10) NOT NULL,active INT NOT NULL DEFAULT 1,created_at VARCHAR(40) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,auth_version INT NOT NULL DEFAULT 1,is_owner INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS church_audit (
 id VARCHAR(100) PRIMARY KEY,created_at VARCHAR(40) NOT NULL,actor_id VARCHAR(100) NOT NULL,
 actor_name VARCHAR(600) NOT NULL,actor_email VARCHAR(254) NOT NULL,action VARCHAR(60) NOT NULL,
 entity VARCHAR(60) NOT NULL,entity_id VARCHAR(100) NOT NULL,details LONGTEXT NOT NULL,
 INDEX audit_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS church_requests (
 id VARCHAR(100) PRIMARY KEY,donation_id VARCHAR(100) NOT NULL,name VARCHAR(400) NOT NULL,
 email VARCHAR(254) NOT NULL,message TEXT NOT NULL,kind VARCHAR(20) NOT NULL,
 created_at VARCHAR(40) NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'open',
 notification VARCHAR(20) NOT NULL DEFAULT 'pending',ip_hash VARCHAR(64) NOT NULL,
 INDEX request_ip (ip_hash,created_at), INDEX request_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS church_auth_limits (
 id VARCHAR(64) PRIMARY KEY,attempts INT NOT NULL,until_at BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS church_content (
 id VARCHAR(100) PRIMARY KEY, kind VARCHAR(20) NOT NULL, title VARCHAR(180) NOT NULL, body MEDIUMTEXT NOT NULL, post_date VARCHAR(10) NOT NULL, published TINYINT NOT NULL DEFAULT 0, image MEDIUMTEXT NOT NULL, revision BIGINT NOT NULL DEFAULT 1, updated_at VARCHAR(40) NOT NULL, last_operation VARCHAR(100) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;