-- AppointmentDesk on PostgreSQL, for the read-only schema MCP server.
-- Run with psql as a superuser, connected to an empty database named appointmentdesk:
--   psql -v reader_password=<a development-only password> -d appointmentdesk -f schema.sql
-- No password is written in this file; psql substitutes :'reader_password'.

-- 1. Tables: the PostgreSQL DDL that EF Core 10 (Npgsql provider 10.0.3) generates from the
--    sample's AppDbContext with Database.GenerateCreateScript(), copied unchanged.
CREATE TABLE "Doctors" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "FullName" character varying(100) NOT NULL,
    "Specialty" character varying(100) NOT NULL,
    CONSTRAINT "PK_Doctors" PRIMARY KEY ("Id")
);

CREATE TABLE "Patients" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "FullName" character varying(100) NOT NULL,
    "Email" character varying(200) NOT NULL,
    CONSTRAINT "PK_Patients" PRIMARY KEY ("Id")
);

CREATE TABLE "Appointments" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "DoctorId" integer NOT NULL,
    "PatientId" integer NOT NULL,
    "StartsAt" timestamp with time zone NOT NULL,
    "Status" character varying(20) NOT NULL,
    CONSTRAINT "PK_Appointments" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Appointments_Doctors_DoctorId" FOREIGN KEY ("DoctorId") REFERENCES "Doctors" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Appointments_Patients_PatientId" FOREIGN KEY ("PatientId") REFERENCES "Patients" ("Id") ON DELETE CASCADE
);

CREATE UNIQUE INDEX "IX_Appointments_DoctorId_StartsAt" ON "Appointments" ("DoctorId", "StartsAt") WHERE "Status" = 'Booked';

CREATE INDEX "IX_Appointments_PatientId" ON "Appointments" ("PatientId");

-- 2. Fictional rows (the same people as the sample's SeedData, plus a few appointments).
INSERT INTO "Doctors" ("FullName", "Specialty") VALUES
    ('Dr. Elena Rossi', 'General practice'),
    ('Dr. Kwame Mensah', 'Paediatrics');

INSERT INTO "Patients" ("FullName", "Email") VALUES
    ('Maria Garcia', 'maria.garcia@example.test'),
    ('David Chen', 'david.chen@example.test'),
    ('Aisha Khan', 'aisha.khan@example.test'),
    (U&'Tom\00E1s Silva', 'tomas.silva@example.test');  -- \00E1 is a with an acute accent; the escape keeps this file plain ASCII

INSERT INTO "Appointments" ("DoctorId", "PatientId", "StartsAt", "Status") VALUES
    (1, 1, '2026-10-03 11:00:00+00', 'Booked'),
    (2, 4, '2026-10-03 15:30:00+00', 'Cancelled'),
    (1, 1, '2026-10-05 09:00:00+00', 'Booked'),
    (1, 2, '2026-10-05 09:15:00+00', 'Cancelled'),
    (2, 3, '2026-10-05 10:30:00+00', 'Booked'),
    (1, 4, '2026-10-06 14:00:00+00', 'Booked'),
    (2, 2, '2026-10-06 16:45:00+00', 'Booked');

-- 3. The role the MCP server connects as: it can log in, connect to this database, see the
--    public schema and read the three tables. Nothing else.
REVOKE ALL ON DATABASE appointmentdesk FROM PUBLIC;   -- new databases grant CONNECT and TEMP to everyone
CREATE ROLE schema_reader LOGIN PASSWORD :'reader_password';
GRANT CONNECT ON DATABASE appointmentdesk TO schema_reader;
GRANT USAGE ON SCHEMA public TO schema_reader;
GRANT SELECT ON "Doctors", "Patients", "Appointments" TO schema_reader;