package db

// migration is a single named schema change with driver-specific SQL variants.
// SQLite is the reference dialect (required); postgres/mysql variants are
// filled in as those build-tagged drivers are added — until then the sqlite
// SQL is close enough to run in most cases (TEXT/BLOB/DATETIME map cleanly).
type migration struct {
	name string
	sql  map[string]string
}

var migrations = []migration{
	{
		name: "0001_tenants_domains",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE tenants (
	id TEXT PRIMARY KEY,
	name TEXT UNIQUE NOT NULL,
	display_name TEXT,
	digest_interval_mins INTEGER NOT NULL DEFAULT 60,
	max_accounts INTEGER NOT NULL DEFAULT 0,
	quota_mb_per_user INTEGER NOT NULL DEFAULT 2048,
	settings_json TEXT NOT NULL DEFAULT '{}',
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE domains (
	id TEXT PRIMARY KEY,
	tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
	domain TEXT UNIQUE NOT NULL,
	active INTEGER NOT NULL DEFAULT 1,
	dkim_selector TEXT,
	dkim_private_key_enc BLOB,
	accept_all INTEGER NOT NULL DEFAULT 1,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_domains_tenant ON domains(tenant_id);
`,
		},
	},
	{
		name: "0002_users_auth",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE users (
	id TEXT PRIMARY KEY,
	tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
	domain_id TEXT NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
	email TEXT UNIQUE NOT NULL,
	password_hash TEXT NOT NULL,
	display_name TEXT,
	role TEXT NOT NULL DEFAULT 'user',
	active INTEGER NOT NULL DEFAULT 1,
	mfa_enabled INTEGER NOT NULL DEFAULT 0,
	totp_secret_enc BLOB,
	passkey_credentials_json TEXT NOT NULL DEFAULT '[]',
	quota_mb INTEGER NOT NULL DEFAULT 2048,
	used_bytes INTEGER NOT NULL DEFAULT 0,
	digest_enabled INTEGER NOT NULL DEFAULT 1,
	digest_interval_mins INTEGER NOT NULL DEFAULT 0,
	last_digest_at DATETIME,
	last_login_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_domain ON users(domain_id);

CREATE TABLE app_passwords (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	label TEXT NOT NULL,
	password_hash TEXT NOT NULL,
	scopes TEXT NOT NULL DEFAULT 'smtp,imap',
	last_used_at DATETIME,
	expires_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_app_passwords_user ON app_passwords(user_id);

CREATE TABLE sessions (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	jti TEXT UNIQUE NOT NULL,
	user_agent TEXT,
	ip TEXT,
	expires_at DATETIME NOT NULL,
	revoked_at DATETIME
);
CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_jti ON sessions(jti);

CREATE TABLE aliases (
	id TEXT PRIMARY KEY,
	tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
	from_address TEXT UNIQUE NOT NULL,
	to_user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
	to_external TEXT,
	active INTEGER NOT NULL DEFAULT 1
);
CREATE INDEX idx_aliases_tenant ON aliases(tenant_id);
`,
		},
	},
	{
		name: "0003_list_rules",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE list_rules (
	id TEXT PRIMARY KEY,
	tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
	list_type TEXT NOT NULL,
	match_type TEXT NOT NULL DEFAULT 'email',
	value TEXT NOT NULL,
	note TEXT,
	active INTEGER NOT NULL DEFAULT 1,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_list_rules_tenant ON list_rules(tenant_id);
CREATE INDEX idx_list_rules_value ON list_rules(value);
`,
		},
	},
	{
		name: "0004_messages_mailbox",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE messages (
	id TEXT PRIMARY KEY,
	tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
	from_address TEXT NOT NULL,
	to_address TEXT NOT NULL,
	subject TEXT,
	message_id_hdr TEXT,
	size_bytes INTEGER NOT NULL DEFAULT 0,
	verdict TEXT NOT NULL DEFAULT 'clean',
	total_score REAL NOT NULL DEFAULT 0,
	sender_ip TEXT,
	relayed_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_messages_tenant ON messages(tenant_id);
CREATE INDEX idx_messages_to ON messages(to_address);
CREATE INDEX idx_messages_created ON messages(created_at);

CREATE TABLE message_checks (
	id TEXT PRIMARY KEY,
	message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
	stage TEXT NOT NULL,
	result TEXT NOT NULL,
	score REAL NOT NULL DEFAULT 0,
	detail TEXT,
	duration_ms INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_message_checks_message ON message_checks(message_id);

CREATE TABLE mailbox_index (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	mailbox TEXT NOT NULL DEFAULT 'INBOX',
	uid INTEGER NOT NULL,
	eml_path TEXT NOT NULL,
	flags TEXT NOT NULL DEFAULT '',
	size_bytes INTEGER NOT NULL DEFAULT 0,
	received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	internal_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE(user_id, mailbox, uid)
);
CREATE INDEX idx_mailbox_index_user ON mailbox_index(user_id, mailbox);

CREATE TABLE mailbox_uid_counters (
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	mailbox TEXT NOT NULL,
	next_uid INTEGER NOT NULL DEFAULT 1,
	PRIMARY KEY (user_id, mailbox)
);
`,
		},
	},
	{
		name: "0005_outbound_queue",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE outbound_queue (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	from_address TEXT NOT NULL,
	to_address TEXT NOT NULL,
	eml_path TEXT NOT NULL,
	priority INTEGER NOT NULL DEFAULT 0,
	attempts INTEGER NOT NULL DEFAULT 0,
	last_error TEXT,
	next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_outbound_queue_next ON outbound_queue(next_attempt_at);
CREATE INDEX idx_outbound_queue_user ON outbound_queue(user_id);
`,
		},
	},
	{
		name: "0006_quarantine",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE quarantine (
	id TEXT PRIMARY KEY,
	message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
	eml_path TEXT NOT NULL,
	status TEXT NOT NULL DEFAULT 'held',
	reason TEXT,
	released_by TEXT,
	released_at DATETIME,
	expires_at DATETIME NOT NULL,
	notified_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_quarantine_status ON quarantine(status);
CREATE INDEX idx_quarantine_message ON quarantine(message_id);

CREATE TABLE release_tokens (
	id TEXT PRIMARY KEY,
	quarantine_id TEXT NOT NULL REFERENCES quarantine(id) ON DELETE CASCADE,
	token TEXT UNIQUE NOT NULL,
	email TEXT,
	used_at DATETIME,
	expires_at DATETIME NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_release_tokens_token ON release_tokens(token);
`,
		},
	},
	{
		name: "0007_linked_accounts",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE linked_accounts (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	provider TEXT NOT NULL,
	display_name TEXT,
	email_address TEXT NOT NULL,
	auth_type TEXT NOT NULL,
	imap_host TEXT,
	imap_port INTEGER,
	imap_tls TEXT,
	smtp_host TEXT,
	smtp_port INTEGER,
	smtp_tls TEXT,
	credential_enc BLOB,
	oauth_expires_at DATETIME,
	sync_state TEXT,
	cache_retention_days INTEGER,
	last_sync_at DATETIME,
	last_sync_error TEXT,
	active INTEGER NOT NULL DEFAULT 1,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_linked_accounts_user ON linked_accounts(user_id);
`,
		},
	},
	{
		name: "0008_dav_contacts_calendars",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE addressbooks (
	id TEXT PRIMARY KEY,
	owner_type TEXT NOT NULL,
	owner_id TEXT NOT NULL,
	display_name TEXT,
	description TEXT,
	sync_token TEXT NOT NULL DEFAULT '1',
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_addressbooks_owner ON addressbooks(owner_type, owner_id);

CREATE TABLE contacts (
	id TEXT PRIMARY KEY,
	addressbook_id TEXT NOT NULL REFERENCES addressbooks(id) ON DELETE CASCADE,
	uid TEXT NOT NULL,
	vcard_enc BLOB NOT NULL,
	etag TEXT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE(addressbook_id, uid)
);
CREATE INDEX idx_contacts_addressbook ON contacts(addressbook_id);

CREATE TABLE calendars (
	id TEXT PRIMARY KEY,
	owner_type TEXT NOT NULL,
	owner_id TEXT NOT NULL,
	display_name TEXT,
	description TEXT,
	color TEXT,
	timezone TEXT NOT NULL DEFAULT 'UTC',
	sync_token TEXT NOT NULL DEFAULT '1',
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_calendars_owner ON calendars(owner_type, owner_id);

CREATE TABLE calendar_objects (
	id TEXT PRIMARY KEY,
	calendar_id TEXT NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
	uid TEXT NOT NULL,
	ical_enc BLOB NOT NULL,
	component_type TEXT,
	summary TEXT,
	dtstart DATETIME,
	dtend DATETIME,
	etag TEXT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE(calendar_id, uid)
);
CREATE INDEX idx_calendar_objects_calendar ON calendar_objects(calendar_id);
`,
		},
	},
	{
		name: "0009_sieve_scripts",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE sieve_scripts (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	name TEXT NOT NULL,
	script_text TEXT NOT NULL,
	active INTEGER NOT NULL DEFAULT 0,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE(user_id, name)
);
CREATE INDEX idx_sieve_scripts_user ON sieve_scripts(user_id);
`,
		},
	},
	{
		name: "0010_tls_certs",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE tls_certs (
	id TEXT PRIMARY KEY,
	domain TEXT UNIQUE NOT NULL,
	cert_pem_enc BLOB,
	key_pem_enc BLOB,
	expires_at DATETIME,
	acme_account_key_enc BLOB,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_tls_certs_domain ON tls_certs(domain);
`,
		},
	},
	{
		name: "0011_mfa_and_recovery",
		sql: map[string]string{
			"sqlite": `
CREATE TABLE mfa_backup_codes (
	id TEXT PRIMARY KEY,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	code_hash TEXT NOT NULL,
	used_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_mfa_backup_codes_user ON mfa_backup_codes(user_id);

ALTER TABLE users ADD COLUMN recovery_email TEXT;
`,
		},
	},
}