CREATE TABLE `billing_addon_plans` (
	`id` text PRIMARY KEY NOT NULL,
	`addon_id` text NOT NULL,
	`plan_id` text NOT NULL,
	FOREIGN KEY (`addon_id`) REFERENCES `billing_addons`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`plan_id`) REFERENCES `billing_plans`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_addon_plans_pair` ON `billing_addon_plans` (`addon_id`,`plan_id`);--> statement-breakpoint
CREATE TABLE `billing_addons` (
	`id` text PRIMARY KEY NOT NULL,
	`slug` text NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`description_ar` text DEFAULT '' NOT NULL,
	`description_en` text DEFAULT '' NOT NULL,
	`price_minor` integer NOT NULL,
	`currency` text DEFAULT 'SAR' NOT NULL,
	`billing_type` text NOT NULL,
	`quantity_unit` text NOT NULL,
	`status` text DEFAULT 'inactive' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_billing_addons_slug` ON `billing_addons` (`slug`);--> statement-breakpoint
CREATE TABLE `billing_audit_logs` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text,
	`actor_user_id` text NOT NULL,
	`action` text NOT NULL,
	`entity_type` text NOT NULL,
	`entity_id` text NOT NULL,
	`old_value_json` text,
	`new_value_json` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_billing_audit_entity` ON `billing_audit_logs` (`entity_type`,`entity_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `idx_billing_audit_workspace` ON `billing_audit_logs` (`workspace_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `billing_features` (
	`key` text PRIMARY KEY NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`description_ar` text DEFAULT '' NOT NULL,
	`description_en` text DEFAULT '' NOT NULL,
	`tooltip_ar` text DEFAULT '' NOT NULL,
	`tooltip_en` text DEFAULT '' NOT NULL,
	`category` text DEFAULT 'platform' NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE `billing_notifications` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`kind` text NOT NULL,
	`threshold` integer,
	`message_ar` text NOT NULL,
	`message_en` text NOT NULL,
	`email_enabled` integer DEFAULT false NOT NULL,
	`read_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_billing_notifications_workspace` ON `billing_notifications` (`workspace_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `billing_plan_entitlements` (
	`id` text PRIMARY KEY NOT NULL,
	`plan_id` text NOT NULL,
	`feature_key` text NOT NULL,
	`enabled` integer DEFAULT false NOT NULL,
	`limit_value` integer,
	`limit_unit` text,
	`note_ar` text DEFAULT '' NOT NULL,
	`note_en` text DEFAULT '' NOT NULL,
	FOREIGN KEY (`plan_id`) REFERENCES `billing_plans`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`feature_key`) REFERENCES `billing_features`(`key`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_plan_entitlements_plan_feature` ON `billing_plan_entitlements` (`plan_id`,`feature_key`);--> statement-breakpoint
CREATE TABLE `billing_plan_versions` (
	`id` text PRIMARY KEY NOT NULL,
	`plan_id` text NOT NULL,
	`version` integer NOT NULL,
	`monthly_price_minor` integer,
	`yearly_price_minor` integer,
	`currency` text NOT NULL,
	`effective_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`created_by` text NOT NULL,
	FOREIGN KEY (`plan_id`) REFERENCES `billing_plans`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_plan_versions_plan_version` ON `billing_plan_versions` (`plan_id`,`version`);--> statement-breakpoint
CREATE TABLE `billing_plans` (
	`id` text PRIMARY KEY NOT NULL,
	`slug` text NOT NULL,
	`name_ar` text NOT NULL,
	`name_en` text NOT NULL,
	`description_ar` text DEFAULT '' NOT NULL,
	`description_en` text DEFAULT '' NOT NULL,
	`monthly_price_minor` integer,
	`yearly_price_minor` integer,
	`currency` text DEFAULT 'SAR' NOT NULL,
	`pricing_mode` text DEFAULT 'fixed' NOT NULL,
	`cta_kind` text DEFAULT 'contact_sales' NOT NULL,
	`popular` integer DEFAULT false NOT NULL,
	`trial_enabled` integer DEFAULT false NOT NULL,
	`status` text DEFAULT 'hidden' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`current_version` integer DEFAULT 1 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_billing_plans_slug` ON `billing_plans` (`slug`);--> statement-breakpoint
CREATE INDEX `idx_billing_plans_status_sort` ON `billing_plans` (`status`,`sort_order`);--> statement-breakpoint
CREATE TABLE `billing_rate_limits` (
	`key` text PRIMARY KEY NOT NULL,
	`window_start` text NOT NULL,
	`request_count` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE `coupon_plans` (
	`id` text PRIMARY KEY NOT NULL,
	`coupon_id` text NOT NULL,
	`plan_id` text NOT NULL,
	FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`plan_id`) REFERENCES `billing_plans`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_coupon_plans_pair` ON `coupon_plans` (`coupon_id`,`plan_id`);--> statement-breakpoint
CREATE TABLE `coupon_redemptions` (
	`id` text PRIMARY KEY NOT NULL,
	`coupon_id` text NOT NULL,
	`workspace_id` text NOT NULL,
	`subscription_id` text,
	`discount_minor` integer NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_coupon_redemptions_coupon_workspace` ON `coupon_redemptions` (`coupon_id`,`workspace_id`);--> statement-breakpoint
CREATE TABLE `coupons` (
	`id` text PRIMARY KEY NOT NULL,
	`code` text NOT NULL,
	`discount_type` text NOT NULL,
	`discount_value` integer NOT NULL,
	`starts_at` text,
	`expires_at` text,
	`usage_limit` integer,
	`per_customer_limit` integer DEFAULT 1 NOT NULL,
	`billing_cycles_json` text DEFAULT '[]' NOT NULL,
	`minimum_amount_minor` integer DEFAULT 0 NOT NULL,
	`active` integer DEFAULT false NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_coupons_code` ON `coupons` (`code`);--> statement-breakpoint
CREATE TABLE `invoice_items` (
	`id` text PRIMARY KEY NOT NULL,
	`invoice_id` text NOT NULL,
	`description` text NOT NULL,
	`quantity` integer DEFAULT 1 NOT NULL,
	`unit_price_minor` integer NOT NULL,
	`total_minor` integer NOT NULL,
	FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `invoices` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`subscription_id` text,
	`invoice_number` text NOT NULL,
	`company_name` text NOT NULL,
	`vat_number` text,
	`issued_at` text NOT NULL,
	`period_start` text,
	`period_end` text,
	`subtotal_minor` integer NOT NULL,
	`vat_minor` integer DEFAULT 0 NOT NULL,
	`total_minor` integer NOT NULL,
	`currency` text NOT NULL,
	`status` text NOT NULL,
	`payment_method_label` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_invoices_number` ON `invoices` (`invoice_number`);--> statement-breakpoint
CREATE INDEX `idx_invoices_workspace` ON `invoices` (`workspace_id`,`issued_at`);--> statement-breakpoint
CREATE TABLE `payment_webhook_events` (
	`id` text PRIMARY KEY NOT NULL,
	`provider` text NOT NULL,
	`provider_event_id` text NOT NULL,
	`status` text DEFAULT 'pending' NOT NULL,
	`payload_hash` text NOT NULL,
	`error_message` text,
	`received_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`processed_at` text
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_webhook_provider_event` ON `payment_webhook_events` (`provider`,`provider_event_id`);--> statement-breakpoint
CREATE TABLE `payments` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`invoice_id` text,
	`provider` text NOT NULL,
	`provider_payment_id` text,
	`amount_minor` integer NOT NULL,
	`currency` text NOT NULL,
	`status` text NOT NULL,
	`idempotency_key` text NOT NULL,
	`failure_code` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_payments_idempotency` ON `payments` (`idempotency_key`);--> statement-breakpoint
CREATE UNIQUE INDEX `idx_payments_provider_id` ON `payments` (`provider`,`provider_payment_id`);--> statement-breakpoint
CREATE TABLE `pricing_settings` (
	`id` text PRIMARY KEY NOT NULL,
	`base_currency` text DEFAULT 'SAR' NOT NULL,
	`vat_enabled` integer DEFAULT false NOT NULL,
	`vat_rate_basis_points` integer DEFAULT 0 NOT NULL,
	`price_includes_vat` integer DEFAULT false NOT NULL,
	`trial_enabled` integer DEFAULT false NOT NULL,
	`trial_days` integer DEFAULT 0 NOT NULL,
	`trial_requires_payment_method` integer DEFAULT false NOT NULL,
	`trial_auto_convert` integer DEFAULT false NOT NULL,
	`warning_thresholds` text DEFAULT '[80,90,100]' NOT NULL,
	`grace_period_days` integer DEFAULT 0 NOT NULL,
	`retention_policy_ar` text DEFAULT 'تحدد سياسة الاحتفاظ بالبيانات في العقد المطبق.' NOT NULL,
	`retention_policy_en` text DEFAULT 'Data retention follows the applicable contract.' NOT NULL,
	`payment_provider` text,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE TABLE `subscription_addons` (
	`id` text PRIMARY KEY NOT NULL,
	`subscription_id` text NOT NULL,
	`addon_id` text NOT NULL,
	`quantity` integer DEFAULT 1 NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`addon_id`) REFERENCES `billing_addons`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `subscriptions` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`plan_id` text NOT NULL,
	`plan_version_id` text NOT NULL,
	`billing_cycle` text NOT NULL,
	`status` text DEFAULT 'pending' NOT NULL,
	`current_period_start` text,
	`current_period_end` text,
	`trial_ends_at` text,
	`cancel_at_period_end` integer DEFAULT false NOT NULL,
	`cancelled_at` text,
	`provider_customer_id` text,
	`provider_subscription_id` text,
	`custom_limits_json` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`plan_id`) REFERENCES `billing_plans`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`plan_version_id`) REFERENCES `billing_plan_versions`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_subscriptions_workspace_status` ON `subscriptions` (`workspace_id`,`status`);--> statement-breakpoint
CREATE TABLE `usage_records` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text NOT NULL,
	`recorded_date` text NOT NULL,
	`agent_id` text,
	`model` text,
	`input_tokens` integer DEFAULT 0 NOT NULL,
	`output_tokens` integer DEFAULT 0 NOT NULL,
	`credits_used` integer DEFAULT 0 NOT NULL,
	`voice_seconds` integer DEFAULT 0 NOT NULL,
	`storage_bytes` integer DEFAULT 0 NOT NULL,
	`messages` integer DEFAULT 0 NOT NULL,
	`campaign_units` integer DEFAULT 0 NOT NULL,
	`api_calls` integer DEFAULT 0 NOT NULL,
	`automation_runs` integer DEFAULT 0 NOT NULL,
	`metadata_json` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_usage_workspace_date` ON `usage_records` (`workspace_id`,`recorded_date`);