CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL) STRICT;

CREATE TABLE parishes (
    id             INTEGER PRIMARY KEY,
    slug           TEXT NOT NULL UNIQUE,
    name           TEXT NOT NULL,
    diocese        TEXT NOT NULL CHECK (diocese IN ('Dallas', 'Fort Worth')),
    street_address TEXT NOT NULL,
    city           TEXT NOT NULL,
    postal_code    TEXT NOT NULL,
    phone          TEXT,
    website_url    TEXT NOT NULL CHECK (website_url LIKE 'https://%' OR website_url LIKE 'http://%')
  ) STRICT;

CREATE TABLE ocia_programs (
    id                    INTEGER PRIMARY KEY,
    parish_id             INTEGER NOT NULL REFERENCES parishes(id) ON DELETE CASCADE,
    name                  TEXT NOT NULL,
    program_year          TEXT NOT NULL CHECK (program_year GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
    language              TEXT NOT NULL DEFAULT 'English',
    meeting_day           TEXT CHECK (meeting_day IN ('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')),
    meeting_time          TEXT CHECK (meeting_time GLOB '[0-2][0-9]:[0-5][0-9]'),
    location              TEXT,
    start_date            TEXT CHECK (start_date GLOB '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'),
    registration_deadline TEXT CHECK (registration_deadline GLOB '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'),
    registration_status   TEXT NOT NULL CHECK (registration_status IN ('open', 'closed', 'unknown')),
    registration_url      TEXT,
    info_url              TEXT NOT NULL,
    contact_name          TEXT,
    contact_email         TEXT,
    contact_phone         TEXT,
    notes                 TEXT,
    source_url            TEXT NOT NULL,
    verified_on           TEXT NOT NULL CHECK (verified_on GLOB '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'),
    UNIQUE (parish_id, program_year, language, name)
  ) STRICT;

CREATE INDEX ocia_programs_parish ON ocia_programs(parish_id);

CREATE VIEW program_scores AS
  SELECT
    p.*,
    (CASE p.registration_status WHEN 'open' THEN 30 WHEN 'unknown' THEN 10 ELSE 0 END)
    + (CASE WHEN p.meeting_day IS NOT NULL AND p.meeting_time IS NOT NULL AND p.location IS NOT NULL THEN 25 ELSE 0 END)
    + (CASE WHEN p.contact_email IS NOT NULL OR p.registration_url IS NOT NULL THEN 20 ELSE 0 END)
    + (CASE WHEN p.verified_on >= date('now', '-90 days') THEN 15 ELSE 0 END)
    + (CASE WHEN p.start_date IS NOT NULL AND p.start_date >= date('now') THEN 10 ELSE 0 END)
    AS score
  FROM ocia_programs p;