CREATE TABLE `attendance_daily_summary` (
	`id` text PRIMARY KEY NOT NULL,
	`organization_id` text NOT NULL,
	`attendance_date` text NOT NULL,
	`checked_in_count` integer DEFAULT 0 NOT NULL,
	`checked_out_count` integer DEFAULT 0 NOT NULL,
	`present_count` integer DEFAULT 0 NOT NULL,
	`late_count` integer DEFAULT 0 NOT NULL,
	`absent_count` integer DEFAULT 0 NOT NULL,
	`peak_occupancy` integer DEFAULT 0 NOT NULL,
	`created_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	`updated_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `attendance_daily_summary_org_date_uidx` ON `attendance_daily_summary` (`organization_id`,`attendance_date`);--> statement-breakpoint
CREATE TABLE `attendance_event` (
	`id` text PRIMARY KEY NOT NULL,
	`organization_id` text NOT NULL,
	`member_id` text NOT NULL,
	`camera_id` text,
	`direction` text NOT NULL,
	`captured_at` integer NOT NULL,
	`method` text NOT NULL,
	`confidence` real,
	`notes` text,
	`created_by_user_id` text,
	`created_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`member_id`) REFERENCES `attendance_member`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`camera_id`) REFERENCES `camera`(`id`) ON UPDATE no action ON DELETE set null,
	FOREIGN KEY (`created_by_user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `attendance_event_org_captured_idx` ON `attendance_event` (`organization_id`,`captured_at`);--> statement-breakpoint
CREATE INDEX `attendance_event_member_captured_idx` ON `attendance_event` (`member_id`,`captured_at`);--> statement-breakpoint
CREATE INDEX `attendance_event_camera_captured_idx` ON `attendance_event` (`camera_id`,`captured_at`);--> statement-breakpoint
CREATE TABLE `attendance_member` (
	`id` text PRIMARY KEY NOT NULL,
	`organization_id` text NOT NULL,
	`member_code` text NOT NULL,
	`first_name` text NOT NULL,
	`last_name` text NOT NULL,
	`email` text,
	`phone` text,
	`department` text,
	`photo_url` text,
	`status` text DEFAULT 'active' NOT NULL,
	`registered_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	`created_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	`updated_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `attendance_member_org_code_uidx` ON `attendance_member` (`organization_id`,`member_code`);--> statement-breakpoint
CREATE UNIQUE INDEX `attendance_member_org_email_uidx` ON `attendance_member` (`organization_id`,`email`);--> statement-breakpoint
CREATE INDEX `attendance_member_org_status_idx` ON `attendance_member` (`organization_id`,`status`);--> statement-breakpoint
CREATE TABLE `camera` (
	`id` text PRIMARY KEY NOT NULL,
	`organization_id` text NOT NULL,
	`name` text NOT NULL,
	`location` text NOT NULL,
	`zone` text,
	`stream_url` text,
	`status` text DEFAULT 'offline' NOT NULL,
	`last_seen_at` integer,
	`created_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	`updated_at` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
	FOREIGN KEY (`organization_id`) REFERENCES `organization`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `camera_org_name_uidx` ON `camera` (`organization_id`,`name`);--> statement-breakpoint
CREATE INDEX `camera_org_status_idx` ON `camera` (`organization_id`,`status`);