CREATE TYPE "books"."reading_status" AS ENUM('want_to_read', 'reading', 'finished', 'abandoned');--> statement-breakpoint
CREATE TABLE "books"."sessions" (
	"id" serial PRIMARY KEY,
	"work_id" integer NOT NULL,
	"file_id" integer,
	"started_at" timestamp with time zone DEFAULT now() NOT NULL,
	"ended_at" timestamp with time zone,
	"start_percent" real,
	"end_percent" real,
	"start_page" integer,
	"end_page" integer,
	"pages_read" integer,
	"source" text DEFAULT 'reader' NOT NULL,
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "sessions_percent_check" CHECK (("start_percent" is null or "start_percent" between 0 and 100) and ("end_percent" is null or "end_percent" between 0 and 100)),
	CONSTRAINT "sessions_end_check" CHECK ("ended_at" is null or "ended_at" >= "started_at")
);
--> statement-breakpoint
ALTER TABLE "books"."progress" ADD COLUMN "status" "books"."reading_status" DEFAULT 'want_to_read'::"books"."reading_status" NOT NULL;--> statement-breakpoint
ALTER TABLE "books"."progress" ADD COLUMN "rating" smallint;--> statement-breakpoint
ALTER TABLE "books"."progress" ADD COLUMN "last_read_at" timestamp with time zone;--> statement-breakpoint
CREATE INDEX "progress_status_idx" ON "books"."progress" ("status");--> statement-breakpoint
CREATE INDEX "sessions_work_id_idx" ON "books"."sessions" ("work_id");--> statement-breakpoint
CREATE INDEX "sessions_started_at_idx" ON "books"."sessions" ("started_at");--> statement-breakpoint
ALTER TABLE "books"."sessions" ADD CONSTRAINT "sessions_work_id_works_id_fkey" FOREIGN KEY ("work_id") REFERENCES "books"."works"("id") ON DELETE CASCADE;--> statement-breakpoint
ALTER TABLE "books"."sessions" ADD CONSTRAINT "sessions_file_id_files_id_fkey" FOREIGN KEY ("file_id") REFERENCES "books"."files"("id") ON DELETE SET NULL;--> statement-breakpoint
ALTER TABLE "books"."progress" ADD CONSTRAINT "progress_rating_check" CHECK ("rating" is null or "rating" between 1 and 5);
--> statement-breakpoint
-- Hand-written: updated_at trigger and explicit grants (default privileges from the books migration cover new tables too).
CREATE TRIGGER touch_updated_at BEFORE UPDATE ON books.sessions FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at();
--> statement-breakpoint
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_app') THEN
    GRANT SELECT, INSERT, UPDATE, DELETE ON books.sessions TO personal_app;
    GRANT USAGE, SELECT ON SEQUENCE books.sessions_id_seq TO personal_app;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_ro') THEN
    GRANT SELECT ON books.sessions TO personal_ro;
  END IF;
END $$;