CREATE TABLE `admins` (
	`id` text PRIMARY KEY NOT NULL,
	`identity` text NOT NULL,
	`email` text NOT NULL,
	`name` text NOT NULL,
	`role` text NOT NULL,
	`active` integer DEFAULT 1 NOT NULL,
	`totp` text,
	`pending_totp` text,
	`last_totp` integer DEFAULT 0 NOT NULL,
	`created_at` text NOT NULL,
	CONSTRAINT "admin_role" CHECK("admins"."role" in ('owner','finance','editor'))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `admins_identity_unique` ON `admins` (`identity`);--> statement-breakpoint
CREATE UNIQUE INDEX `admins_email_unique` ON `admins` (`email`);--> statement-breakpoint
CREATE TABLE `audit` (
	`id` text PRIMARY KEY NOT NULL,
	`actor` text NOT NULL,
	`action` text NOT NULL,
	`entity` text NOT NULL,
	`detail` text NOT NULL,
	`created_at` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `audit_date` ON `audit` (`created_at`);--> statement-breakpoint
CREATE TABLE `backup_jobs` (
	`id` text PRIMARY KEY NOT NULL,
	`started_at` text NOT NULL,
	`finished_at` text,
	`status` text NOT NULL,
	`scope` text NOT NULL,
	`manifest_key` text,
	`expires_at` text NOT NULL,
	`error` text,
	`actor` text NOT NULL
);
--> statement-breakpoint
CREATE TABLE `bank_account` (
	`id` text PRIMARY KEY NOT NULL,
	`bank` text NOT NULL,
	`number` text NOT NULL,
	`holder` text NOT NULL,
	`confirmed` integer DEFAULT 0 NOT NULL,
	`source_media_id` text,
	`confirmed_by` text,
	`updated_at` text NOT NULL,
	FOREIGN KEY (`source_media_id`) REFERENCES `media`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `bank_transactions` (
	`id` text PRIMARY KEY NOT NULL,
	`import_id` text,
	`account_id` text NOT NULL,
	`date` text NOT NULL,
	`credit` integer DEFAULT 0 NOT NULL,
	`debit` integer DEFAULT 0 NOT NULL,
	`description` text NOT NULL,
	`reference` text,
	`fingerprint` text NOT NULL,
	`duplicate_candidate` integer DEFAULT 0 NOT NULL,
	`duplicate_resolved` integer DEFAULT 0 NOT NULL,
	`scope` text DEFAULT 'unidentified' NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`import_id`) REFERENCES `bank_imports`(`id`) ON UPDATE no action ON DELETE no action,
	CONSTRAINT "bank_positive" CHECK("bank_transactions"."credit">=0 AND "bank_transactions"."debit">=0 AND ("bank_transactions"."credit">0 OR "bank_transactions"."debit">0))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `bank_reference` ON `bank_transactions` (`account_id`,`reference`);--> statement-breakpoint
CREATE INDEX `bank_fingerprint` ON `bank_transactions` (`fingerprint`);--> statement-breakpoint
CREATE INDEX `bank_date` ON `bank_transactions` (`account_id`,`date`);--> statement-breakpoint
CREATE TABLE `donations` (
	`id` text PRIMARY KEY NOT NULL,
	`token_hash` text NOT NULL,
	`program_id` text,
	`amount` integer NOT NULL,
	`name` text NOT NULL,
	`contact` text,
	`message` text,
	`publish_name` integer DEFAULT 0 NOT NULL,
	`publish_message` integer DEFAULT 0 NOT NULL,
	`moderated` integer DEFAULT 0 NOT NULL,
	`status` text DEFAULT 'waiting_payment' NOT NULL,
	`source` text DEFAULT 'online' NOT NULL,
	`created_at` text NOT NULL,
	`expires_at` text NOT NULL,
	`verified_at` text,
	`receipt_snapshot` text,
	`reason` text,
	`request_key` text,
	FOREIGN KEY (`program_id`) REFERENCES `records`(`id`) ON UPDATE no action ON DELETE no action,
	CONSTRAINT "positive_donation" CHECK("donations"."amount">0 AND typeof("donations"."amount")='integer'),
	CONSTRAINT "donation_status" CHECK("donations"."status" IN ('waiting_payment','waiting_verification','verified','rejected','expired','refunded'))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `donations_token_hash_unique` ON `donations` (`token_hash`);--> statement-breakpoint
CREATE UNIQUE INDEX `donations_request_key_unique` ON `donations` (`request_key`);--> statement-breakpoint
CREATE INDEX `donations_status_date` ON `donations` (`status`,`created_at`);--> statement-breakpoint
CREATE TABLE `bank_imports` (
	`id` text PRIMARY KEY NOT NULL,
	`account_id` text NOT NULL,
	`source` text NOT NULL,
	`from_date` text NOT NULL,
	`to_date` text NOT NULL,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL
);
--> statement-breakpoint
CREATE TABLE `invitations` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`role` text NOT NULL,
	`created_at` text NOT NULL,
	`created_by` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `invitations_email_unique` ON `invitations` (`email`);--> statement-breakpoint
CREATE TABLE `ledger` (
	`id` text PRIMARY KEY NOT NULL,
	`kind` text NOT NULL,
	`amount` integer NOT NULL,
	`program_id` text,
	`donation_id` text,
	`bank_id` text,
	`source_key` text NOT NULL,
	`related_id` text,
	`pair_id` text,
	`date` text NOT NULL,
	`description` text NOT NULL,
	`category` text,
	`private_media_id` text,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`program_id`) REFERENCES `records`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`donation_id`) REFERENCES `donations`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`bank_id`) REFERENCES `bank_transactions`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`private_media_id`) REFERENCES `media`(`id`) ON UPDATE no action ON DELETE no action,
	CONSTRAINT "ledger_amount" CHECK("ledger"."amount">0 AND typeof("ledger"."amount")='integer'),
	CONSTRAINT "ledger_kind" CHECK("ledger"."kind" IN ('receipt','receipt_reversal','expense','fee','refund','correction_credit','allocation_in','allocation_out'))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `ledger_source_key_unique` ON `ledger` (`source_key`);--> statement-breakpoint
CREATE INDEX `ledger_program_date` ON `ledger` (`program_id`,`date`);--> statement-breakpoint
CREATE TABLE `bank_matches` (
	`id` text PRIMARY KEY NOT NULL,
	`bank_id` text NOT NULL,
	`donation_id` text NOT NULL,
	`amount` integer NOT NULL,
	`reason` text NOT NULL,
	`created_by` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`bank_id`) REFERENCES `bank_transactions`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`donation_id`) REFERENCES `donations`(`id`) ON UPDATE no action ON DELETE no action,
	CONSTRAINT "positive_match" CHECK("bank_matches"."amount">0)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `bank_donation_match` ON `bank_matches` (`bank_id`,`donation_id`);--> statement-breakpoint
CREATE TABLE `media` (
	`id` text PRIMARY KEY NOT NULL,
	`key` text NOT NULL,
	`name` text NOT NULL,
	`mime` text NOT NULL,
	`size` integer NOT NULL,
	`visibility` text NOT NULL,
	`caption` text DEFAULT '' NOT NULL,
	`alt` text DEFAULT '' NOT NULL,
	`donation_id` text,
	`created_by` text,
	`created_at` text NOT NULL,
	FOREIGN KEY (`donation_id`) REFERENCES `donations`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `media_key_unique` ON `media` (`key`);--> statement-breakpoint
CREATE INDEX `media_visibility` ON `media` (`visibility`);--> statement-breakpoint
CREATE INDEX `media_donation` ON `media` (`donation_id`);--> statement-breakpoint
CREATE TABLE `operational` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text NOT NULL,
	`updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE TABLE `rate_limits` (
	`key` text PRIMARY KEY NOT NULL,
	`count` integer NOT NULL,
	`reset_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `records` (
	`id` text PRIMARY KEY NOT NULL,
	`type` text NOT NULL,
	`slug` text NOT NULL,
	`state` text DEFAULT 'draft' NOT NULL,
	`revision` integer DEFAULT 1 NOT NULL,
	`payload` text NOT NULL,
	`live` text,
	`updated_at` text NOT NULL,
	`published_at` text,
	`updated_by` text
);
--> statement-breakpoint
CREATE UNIQUE INDEX `records_type_slug` ON `records` (`type`,`slug`);--> statement-breakpoint
CREATE INDEX `records_public` ON `records` (`type`,`state`);--> statement-breakpoint
CREATE TABLE `recovery_codes` (
	`hash` text PRIMARY KEY NOT NULL,
	`admin_id` text NOT NULL,
	`used_at` text,
	FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `restore_jobs` (
	`id` text PRIMARY KEY NOT NULL,
	`backup_id` text NOT NULL,
	`status` text NOT NULL,
	`scope` text NOT NULL,
	`reason` text NOT NULL,
	`actor` text NOT NULL,
	`created_at` text NOT NULL,
	`detail` text
);
--> statement-breakpoint
CREATE TABLE `sessions` (
	`hash` text PRIMARY KEY NOT NULL,
	`admin_id` text NOT NULL,
	`factor` integer DEFAULT 0 NOT NULL,
	`expires` text NOT NULL,
	`reauth_at` text,
	`created_at` text NOT NULL,
	FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `sessions_admin` ON `sessions` (`admin_id`);--> statement-breakpoint
CREATE TABLE `content_versions` (
	`id` text PRIMARY KEY NOT NULL,
	`record_id` text NOT NULL,
	`revision` integer NOT NULL,
	`snapshot` text NOT NULL,
	`state` text NOT NULL,
	`summary` text NOT NULL,
	`actor` text NOT NULL,
	`created_at` text NOT NULL,
	FOREIGN KEY (`record_id`) REFERENCES `records`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `content_version_revision` ON `content_versions` (`record_id`,`revision`);