CREATE TABLE `subscription_plans` (
	`id` text PRIMARY KEY NOT NULL,
	`plan_id` text NOT NULL,
	`name` text NOT NULL,
	`period` text NOT NULL,
	`price_per_period_minor` integer NOT NULL,
	`currency` text NOT NULL,
	`effective_from` text NOT NULL,
	`active` integer DEFAULT true NOT NULL,
	`created_by` text,
	`created_at` text DEFAULT (current_timestamp) NOT NULL
);
--> statement-breakpoint
CREATE INDEX `subscription_plans_plan_id_idx` ON `subscription_plans` (`plan_id`);--> statement-breakpoint
-- A subscription now records WHICH plan + WHICH immutable plan version priced it, so
-- the sale reprices identically later (like a payment's tariff_version_id). Null for
-- legacy/comp rows. SQLite ALTER ADD COLUMN is in-place and safe for existing rows.
ALTER TABLE `subscriptions` ADD `plan_id` text;--> statement-breakpoint
ALTER TABLE `subscriptions` ADD `plan_version_id` text;--> statement-breakpoint
-- Seed a "Monthly" plan from the existing site default price so sites that already set
-- one keep their monthly plan with no data loss. period='month'; effective at epoch so
-- it always resolves. Skipped when no site default is set (no priced plan to seed).
INSERT INTO `subscription_plans`
	(`id`, `plan_id`, `name`, `period`, `price_per_period_minor`, `currency`, `effective_from`, `active`)
SELECT
	'plan-monthly-seed', 'monthly', 'Monthly', 'month',
	`subscription_monthly_price_minor`,
	'ALL',
	'1970-01-01T00:00:00.000Z', 1
FROM `site_config`
WHERE `subscription_monthly_price_minor` IS NOT NULL
LIMIT 1;--> statement-breakpoint
-- Grant the new subscription:plan permission to the built-in admin role (admin is
-- runtime-special-cased to ALL permissions, but the Roles UI lists the grid from these
-- rows — keep it in sync). INSERT OR IGNORE: harmless if the row already exists.
INSERT OR IGNORE INTO `role_permissions` (`role_id`, `permission`) VALUES ('admin','subscription:plan');