CREATE TYPE "account_role" AS ENUM('admin', 'manager', 'staff');--> statement-breakpoint
CREATE TYPE "occurrence" AS ENUM('once', 'daily', 'weekly', 'monthly', 'quarterly', 'general');--> statement-breakpoint
CREATE TYPE "task_status" AS ENUM('todo', 'started', 'finished', 'checked', 'missed');--> statement-breakpoint
CREATE TYPE "work_pattern" AS ENUM('mon_fri', 'mon_sat');--> statement-breakpoint
CREATE TABLE "accounts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"person_id" uuid NOT NULL UNIQUE,
	"username" text UNIQUE,
	"password_hash" text NOT NULL,
	"role" "account_role" DEFAULT 'staff'::"account_role" NOT NULL,
	"last_login_at" timestamp with time zone,
	"disabled_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "departments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"name" text NOT NULL UNIQUE,
	"archived_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "holidays" (
	"holiday_date" date PRIMARY KEY,
	"name" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "people" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"employee_code" text NOT NULL UNIQUE,
	"first_name" text NOT NULL,
	"last_name" text NOT NULL,
	"position_id" uuid,
	"work_pattern" "work_pattern" DEFAULT 'mon_fri'::"work_pattern" NOT NULL,
	"archived_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "positions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"department_id" uuid NOT NULL,
	"title" text NOT NULL,
	"job_description" text,
	"archived_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "recurring_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"position_id" uuid NOT NULL,
	"description" text NOT NULL,
	"occurrence" "occurrence" NOT NULL,
	"deadline" timestamp with time zone,
	"day_of_week" smallint,
	"day_of_month" smallint,
	"checker_person_id" uuid NOT NULL,
	"verifier_person_id" uuid,
	"report_to_deliver" text,
	"report_example_url" text,
	"wi_sop_url" text,
	"time_to_do_minutes" integer,
	"customer_tag" text,
	"active" boolean DEFAULT true NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "recurring_templates_day_of_week_range" CHECK ("day_of_week" IS NULL OR "day_of_week" BETWEEN 1 AND 7),
	CONSTRAINT "recurring_templates_day_of_month_range" CHECK ("day_of_month" IS NULL OR "day_of_month" BETWEEN 1 AND 31),
	CONSTRAINT "recurring_templates_schedule_matches_occurrence" CHECK (
        CASE "occurrence"
          WHEN 'once' THEN "deadline" IS NOT NULL
            AND "day_of_week" IS NULL AND "day_of_month" IS NULL
          WHEN 'daily' THEN "deadline" IS NULL
            AND "day_of_week" IS NULL AND "day_of_month" IS NULL
          WHEN 'weekly' THEN "deadline" IS NULL
            AND "day_of_week" IS NOT NULL AND "day_of_month" IS NULL
          WHEN 'monthly' THEN "deadline" IS NULL
            AND "day_of_week" IS NULL AND "day_of_month" IS NOT NULL
          WHEN 'quarterly' THEN "deadline" IS NULL
            AND "day_of_week" IS NULL AND "day_of_month" IS NOT NULL
          WHEN 'general' THEN "deadline" IS NULL
            AND "day_of_week" IS NULL AND "day_of_month" IS NULL
        END
      )
);
--> statement-breakpoint
CREATE TABLE "task_instances" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"recurring_template_id" uuid NOT NULL,
	"due_date" date NOT NULL,
	"assignee_person_id" uuid,
	"status" "task_status" DEFAULT 'todo'::"task_status" NOT NULL,
	"started_at" timestamp with time zone,
	"finished_at" timestamp with time zone,
	"checked_at" timestamp with time zone,
	"checked_by" uuid,
	"verified_at" timestamp with time zone,
	"verified_by" uuid,
	"sent_back_at" timestamp with time zone,
	"sent_back_by" uuid,
	"sent_back_note" text,
	"issue_reported_at" timestamp with time zone,
	"issue_reported_by" uuid,
	"issue_note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "task_instances_verified_after_checked" CHECK ("verified_at" IS NULL OR "checked_at" IS NOT NULL),
	CONSTRAINT "task_instances_checked_is_not_missed" CHECK ("status" <> 'missed' OR "checked_at" IS NULL)
);
--> statement-breakpoint
CREATE TABLE "task_submissions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"task_instance_id" uuid NOT NULL UNIQUE,
	"submitted_by" uuid NOT NULL,
	"submitted_at" timestamp with time zone DEFAULT now() NOT NULL,
	"note" text NOT NULL,
	"readings" jsonb,
	"delivered_name" text,
	"delivered_url" text,
	"delivered_how" text
);
--> statement-breakpoint
CREATE UNIQUE INDEX "positions_department_title_key" ON "positions" ("department_id","title");--> statement-breakpoint
CREATE INDEX "recurring_templates_position_idx" ON "recurring_templates" ("position_id");--> statement-breakpoint
CREATE UNIQUE INDEX "task_instances_template_due_key" ON "task_instances" ("recurring_template_id","due_date");--> statement-breakpoint
CREATE INDEX "task_instances_assignee_due_idx" ON "task_instances" ("assignee_person_id","due_date");--> statement-breakpoint
CREATE INDEX "task_instances_status_due_idx" ON "task_instances" ("status","due_date");--> statement-breakpoint
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_person_id_people_id_fkey" FOREIGN KEY ("person_id") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "people" ADD CONSTRAINT "people_position_id_positions_id_fkey" FOREIGN KEY ("position_id") REFERENCES "positions"("id");--> statement-breakpoint
ALTER TABLE "positions" ADD CONSTRAINT "positions_department_id_departments_id_fkey" FOREIGN KEY ("department_id") REFERENCES "departments"("id");--> statement-breakpoint
ALTER TABLE "recurring_templates" ADD CONSTRAINT "recurring_templates_position_id_positions_id_fkey" FOREIGN KEY ("position_id") REFERENCES "positions"("id");--> statement-breakpoint
ALTER TABLE "recurring_templates" ADD CONSTRAINT "recurring_templates_checker_person_id_people_id_fkey" FOREIGN KEY ("checker_person_id") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "recurring_templates" ADD CONSTRAINT "recurring_templates_verifier_person_id_people_id_fkey" FOREIGN KEY ("verifier_person_id") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "recurring_templates" ADD CONSTRAINT "recurring_templates_created_by_people_id_fkey" FOREIGN KEY ("created_by") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_ChTF0cD0rNa5_fkey" FOREIGN KEY ("recurring_template_id") REFERENCES "recurring_templates"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_assignee_person_id_people_id_fkey" FOREIGN KEY ("assignee_person_id") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_checked_by_people_id_fkey" FOREIGN KEY ("checked_by") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_verified_by_people_id_fkey" FOREIGN KEY ("verified_by") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_sent_back_by_people_id_fkey" FOREIGN KEY ("sent_back_by") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_instances" ADD CONSTRAINT "task_instances_issue_reported_by_people_id_fkey" FOREIGN KEY ("issue_reported_by") REFERENCES "people"("id");--> statement-breakpoint
ALTER TABLE "task_submissions" ADD CONSTRAINT "task_submissions_task_instance_id_task_instances_id_fkey" FOREIGN KEY ("task_instance_id") REFERENCES "task_instances"("id");--> statement-breakpoint
ALTER TABLE "task_submissions" ADD CONSTRAINT "task_submissions_submitted_by_people_id_fkey" FOREIGN KEY ("submitted_by") REFERENCES "people"("id");