73 lines
3.8 KiB
SQL
73 lines
3.8 KiB
SQL
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`); |