CREATE TABLE `application_activities` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`application_id` integer NOT NULL,
	`type` text NOT NULL,
	`title` text NOT NULL,
	`detail` text DEFAULT '' NOT NULL,
	`actor` text DEFAULT 'Compétences+' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`application_id`) REFERENCES `applications`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `activities_application_idx` ON `application_activities` (`application_id`);--> statement-breakpoint
CREATE TABLE `application_documents` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`application_id` integer NOT NULL,
	`code` text NOT NULL,
	`label` text NOT NULL,
	`status` text DEFAULT 'missing' NOT NULL,
	`note` text DEFAULT '' NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`application_id`) REFERENCES `applications`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `documents_application_idx` ON `application_documents` (`application_id`);--> statement-breakpoint
CREATE UNIQUE INDEX `documents_application_code_unique` ON `application_documents` (`application_id`,`code`);--> statement-breakpoint
CREATE TABLE `applications` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`reference` text NOT NULL,
	`full_name` text NOT NULL,
	`phone` text NOT NULL,
	`email` text DEFAULT '' NOT NULL,
	`city` text NOT NULL,
	`preferred_contact` text NOT NULL,
	`job` text NOT NULL,
	`experience_code` text NOT NULL,
	`experience_label` text NOT NULL,
	`professional_field` text NOT NULL,
	`education_code` text NOT NULL,
	`education_label` text NOT NULL,
	`target_code` text NOT NULL,
	`target_label` text NOT NULL,
	`missions` text NOT NULL,
	`language` text DEFAULT 'ar' NOT NULL,
	`status` text DEFAULT 'new' NOT NULL,
	`advisor` text DEFAULT 'غير معيّن' NOT NULL,
	`next_action` text DEFAULT 'التواصل الأولي مع المترشح' NOT NULL,
	`internal_note` text DEFAULT '' NOT NULL,
	`eligibility_score` integer DEFAULT 0 NOT NULL,
	`eligibility_tone` text DEFAULT 'start' NOT NULL,
	`eligibility_title` text DEFAULT '' NOT NULL,
	`eligibility_summary` text DEFAULT '' NOT NULL,
	`eligibility_recommendation` text DEFAULT '' NOT NULL,
	`eligibility_strengths` text DEFAULT '[]' NOT NULL,
	`eligibility_missing` text DEFAULT '[]' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `applications_reference_unique` ON `applications` (`reference`);--> statement-breakpoint
CREATE INDEX `applications_email_idx` ON `applications` (`email`);--> statement-breakpoint
CREATE INDEX `applications_status_idx` ON `applications` (`status`);--> statement-breakpoint
CREATE INDEX `applications_created_at_idx` ON `applications` (`created_at`);