CREATE TABLE `audit_log` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`staff_id` text,
	`action` text NOT NULL,
	`entity` text NOT NULL,
	`entity_id` text,
	`before` text,
	`after` text,
	`device_id` text,
	`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE INDEX `audit_entity_idx` ON `audit_log` (`entity`,`entity_id`);--> statement-breakpoint
CREATE TABLE `branches` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`address` text,
	`timezone` text DEFAULT 'Asia/Tehran' NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `cash_movements` (
	`id` text PRIMARY KEY NOT NULL,
	`shift_id` text NOT NULL,
	`type` text NOT NULL,
	`amount` integer NOT NULL,
	`reason` text NOT NULL,
	`staff_id` text NOT NULL,
	`created_at` integer NOT NULL,
	FOREIGN KEY (`shift_id`) REFERENCES `shifts`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `customer_aliases` (
	`provisional_id` text PRIMARY KEY NOT NULL,
	`canonical_id` text NOT NULL,
	`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `customers` (
	`id` text PRIMARY KEY NOT NULL,
	`phone` text NOT NULL,
	`name` text NOT NULL,
	`tier` text DEFAULT 'bronze' NOT NULL,
	`birth_date` text,
	`created_branch_id` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`provisional` integer DEFAULT false NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `customers_phone_uq` ON `customers` (`phone`);--> statement-breakpoint
CREATE TABLE `devices` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`type` text NOT NULL,
	`token_hash` text NOT NULL,
	`pc_id` text,
	`paired_at` integer NOT NULL,
	`revoked_at` integer,
	`last_seen` integer,
	FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`pc_id`) REFERENCES `pcs`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `dynamic_pricing_rules` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`name` text NOT NULL,
	`priority` integer DEFAULT 100 NOT NULL,
	`enabled` integer DEFAULT false NOT NULL,
	`conditions` text NOT NULL,
	`action` text NOT NULL,
	`floor_rate` integer
);
--> statement-breakpoint
CREATE TABLE `game_play_events` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`session_id` text NOT NULL,
	`customer_id` text,
	`game_id` text NOT NULL,
	`pc_id` text NOT NULL,
	`process_name` text NOT NULL,
	`started_at` integer NOT NULL,
	`ended_at` integer,
	`seconds` integer
);
--> statement-breakpoint
CREATE INDEX `gpe_session_idx` ON `game_play_events` (`session_id`);--> statement-breakpoint
CREATE INDEX `gpe_customer_idx` ON `game_play_events` (`customer_id`);--> statement-breakpoint
CREATE INDEX `gpe_game_idx` ON `game_play_events` (`game_id`);--> statement-breakpoint
CREATE TABLE `games` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`title` text NOT NULL,
	`launch_type` text NOT NULL,
	`exe_path` text,
	`launch_uri` text,
	`args` text,
	`process_names` text NOT NULL,
	`cover_url` text,
	`category` text,
	`enabled` integer DEFAULT true NOT NULL,
	`sort` integer DEFAULT 0 NOT NULL,
	FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `order_items` (
	`id` text PRIMARY KEY NOT NULL,
	`order_id` text NOT NULL,
	`product_id` text NOT NULL,
	`qty` integer NOT NULL,
	`unit_price` integer NOT NULL,
	FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE TABLE `orders` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`session_id` text,
	`customer_id` text,
	`status` text NOT NULL,
	`total` integer NOT NULL,
	`paid_via` text NOT NULL,
	`staff_id` text,
	`source` text NOT NULL,
	`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE INDEX `orders_session_idx` ON `orders` (`session_id`);--> statement-breakpoint
CREATE TABLE `pairing_codes` (
	`code` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`device_type` text NOT NULL,
	`created_by` text NOT NULL,
	`expires_at` integer NOT NULL,
	`used_at` integer
);
--> statement-breakpoint
CREATE TABLE `pcs` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`zone_id` text NOT NULL,
	`name` text NOT NULL,
	`mac` text,
	`static_ip` text,
	`device_id` text,
	`status` text DEFAULT 'offline' NOT NULL,
	`specs` text,
	`enabled` integer DEFAULT true NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`zone_id`) REFERENCES `zones`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `pcs_zone_idx` ON `pcs` (`zone_id`);--> statement-breakpoint
CREATE TABLE `points_ledger` (
	`id` text PRIMARY KEY NOT NULL,
	`customer_id` text NOT NULL,
	`branch_id` text,
	`type` text NOT NULL,
	`points` integer NOT NULL,
	`ref_type` text,
	`ref_id` text,
	`idempotency_key` text NOT NULL,
	`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `points_idem_uq` ON `points_ledger` (`idempotency_key`);--> statement-breakpoint
CREATE INDEX `points_customer_idx` ON `points_ledger` (`customer_id`);--> statement-breakpoint
CREATE TABLE `pricing_rate_cards` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`zone_id` text NOT NULL,
	`tier` text,
	`price_per_hour` integer NOT NULL,
	`effective_from` integer NOT NULL
);
--> statement-breakpoint
CREATE INDEX `rate_cards_zone_idx` ON `pricing_rate_cards` (`zone_id`);--> statement-breakpoint
CREATE TABLE `products` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`name` text NOT NULL,
	`price` integer NOT NULL,
	`category` text,
	`stock_qty` integer,
	`active` integer DEFAULT true NOT NULL
);
--> statement-breakpoint
CREATE TABLE `session_segments` (
	`id` text PRIMARY KEY NOT NULL,
	`session_id` text NOT NULL,
	`pc_id` text NOT NULL,
	`zone_id` text NOT NULL,
	`rate_per_hour` integer NOT NULL,
	`applied_rule_ids` text NOT NULL,
	`started_at` integer NOT NULL,
	`ended_at` integer,
	`cost` integer DEFAULT 0 NOT NULL,
	FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `segments_session_idx` ON `session_segments` (`session_id`);--> statement-breakpoint
CREATE TABLE `sessions` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`customer_id` text,
	`mode` text NOT NULL,
	`status` text NOT NULL,
	`started_at` integer NOT NULL,
	`ended_at` integer,
	`prepaid_minutes` integer,
	`total_cost` integer DEFAULT 0 NOT NULL,
	`opened_by_staff` text NOT NULL,
	`notes` text
);
--> statement-breakpoint
CREATE INDEX `sessions_status_idx` ON `sessions` (`status`);--> statement-breakpoint
CREATE INDEX `sessions_customer_idx` ON `sessions` (`customer_id`);--> statement-breakpoint
CREATE TABLE `settings` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `shifts` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`staff_id` text NOT NULL,
	`opened_at` integer NOT NULL,
	`closed_at` integer,
	`opening_cash` integer NOT NULL,
	`counted_cash` integer,
	`expected_cash` integer,
	`z_report` text
);
--> statement-breakpoint
CREATE TABLE `staff` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`name` text NOT NULL,
	`phone` text,
	`username` text NOT NULL,
	`password_hash` text NOT NULL,
	`role` text NOT NULL,
	`active` integer DEFAULT true NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `staff_username_uq` ON `staff` (`username`);--> statement-breakpoint
CREATE TABLE `sync_applied` (
	`event_id` text PRIMARY KEY NOT NULL,
	`applied_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `sync_cursors` (
	`stream_name` text PRIMARY KEY NOT NULL,
	`last_event_id` text,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `sync_outbox` (
	`event_id` text PRIMARY KEY NOT NULL,
	`aggregate_type` text NOT NULL,
	`aggregate_id` text NOT NULL,
	`event_type` text NOT NULL,
	`payload` text NOT NULL,
	`created_at` integer NOT NULL,
	`acked_at` integer
);
--> statement-breakpoint
CREATE INDEX `outbox_unacked_idx` ON `sync_outbox` (`acked_at`,`event_id`);--> statement-breakpoint
CREATE TABLE `wallet_ledger` (
	`id` text PRIMARY KEY NOT NULL,
	`customer_id` text NOT NULL,
	`branch_id` text,
	`type` text NOT NULL,
	`amount` integer NOT NULL,
	`ref_type` text,
	`ref_id` text,
	`reason` text,
	`idempotency_key` text NOT NULL,
	`created_by` text NOT NULL,
	`device_id` text,
	`prev_hash` text NOT NULL,
	`row_hash` text NOT NULL,
	`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `wallet_idem_uq` ON `wallet_ledger` (`idempotency_key`);--> statement-breakpoint
CREATE INDEX `wallet_customer_idx` ON `wallet_ledger` (`customer_id`);--> statement-breakpoint
CREATE TABLE `zones` (
	`id` text PRIMARY KEY NOT NULL,
	`branch_id` text NOT NULL,
	`name` text NOT NULL,
	`sort` integer DEFAULT 0 NOT NULL,
	FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON UPDATE no action ON DELETE no action
);
