-- Neighborhood app -- MySQL schema.
--
-- This is the MysqliDatabase upgrade path from the default FileDatabase
-- (JSON file) storage -- see BACKEND_SETUP.md for when/how to switch. Run
-- this ONCE against an empty database dedicated to this app (do not point
-- it at an existing Airbridge app's database -- e.g. Weddings' own
-- `airbridg_photo_slots` -- this is a separate app with its own schema).
--
-- Requires MySQL 5.7+ or MariaDB 10.2+ (JSON column type).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS neighborhoods (
  id                CHAR(36)      NOT NULL PRIMARY KEY,
  name              VARCHAR(191)  NOT NULL,
  city              VARCHAR(120)  NOT NULL,
  state             VARCHAR(60)   NOT NULL,
  zip               VARCHAR(20)   NOT NULL DEFAULT '',
  god_host_id       VARCHAR(64)   NOT NULL,
  -- { superHostCanApprove, hostCanApprove, superHostCanEditStreets, hostCanEditStreets }
  permissions       JSON          NOT NULL,
  -- A satellite map image (e.g. stitched with the SnapStitch browser
  -- extension) used instead of Google Maps -- see `lots` below for the
  -- polygons traced on top of it. NULL until the Platform Host uploads one.
  map_image_path    VARCHAR(500)  NULL,
  map_image_width   INT           NULL,
  map_image_height  INT           NULL,
  created_at        DATETIME      NOT NULL,
  updated_at        DATETIME      NULL,
  KEY idx_god_host (god_host_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Super Host / Host designations. The Platform Host itself is a single column on
-- `neighborhoods` (god_host_id), not a row here, matching the app's
-- "exactly one Platform Host per neighborhood" rule.
CREATE TABLE IF NOT EXISTS neighborhood_roles (
  neighborhood_id  CHAR(36)                        NOT NULL,
  member_id        VARCHAR(64)                      NOT NULL,
  role             ENUM('SUPER_HOST','HOST')       NOT NULL,
  created_at       DATETIME                        NOT NULL,
  PRIMARY KEY (neighborhood_id, member_id),
  CONSTRAINT fk_roles_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Display-name directory for the Platform Host + every approved member, so the
-- admin panel's member/role list has a name to show without joining out to
-- the Airbridge Universe for every request.
-- access_token is this person's per-neighborhood bearer credential (handed
-- back exactly once -- at neighborhood creation for the Platform Host, at
-- membership approval for everyone else -- see NeighborhoodService /
-- MembershipService). Sent by the client as the custom X-Member-Token
-- header (Authorization is stripped by this host's PHP execution mode,
-- same reasoning as AdminAuthService's X-Admin-Token).
CREATE TABLE IF NOT EXISTS neighborhood_people (
  neighborhood_id  CHAR(36)      NOT NULL,
  member_id        VARCHAR(64)   NOT NULL,
  name             VARCHAR(191)  NOT NULL,
  access_token     CHAR(36)      NOT NULL,
  PRIMARY KEY (neighborhood_id, member_id),
  UNIQUE KEY uniq_people_token (access_token),
  CONSTRAINT fk_people_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The "Neighborhood domain": one row per defined street segment (a street
-- can have more than one row -- e.g. two different address ranges on the
-- same named street). `path` is the drawn street geometry (array of
-- {lat,lng}) captured from OpenStreetMap at the time the Platform Host added it.
CREATE TABLE IF NOT EXISTS streets (
  id               CHAR(36)      NOT NULL PRIMARY KEY,
  neighborhood_id  CHAR(36)      NOT NULL,
  name             VARCHAR(191)  NOT NULL,
  begin_number     INT           NULL,
  end_number       INT           NULL,
  all_addresses    TINYINT(1)    NOT NULL DEFAULT 0,
  color            CHAR(7)       NOT NULL DEFAULT '#2e7d32',
  path             JSON          NOT NULL,
  added_at         DATETIME      NOT NULL,
  KEY idx_streets_neighborhood (neighborhood_id),
  KEY idx_streets_name (neighborhood_id, name),
  CONSTRAINT fk_streets_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Lot boundaries hand-traced on `neighborhoods.map_image_path`, in that
-- image's own pixel coordinates -- the satellite-map equivalent of a
-- `streets` row, used by the same address_key lookups everywhere else in
-- the schema. Every lot on the same street shares a color, same rule as
-- streets.
CREATE TABLE IF NOT EXISTS lots (
  id               CHAR(36)      NOT NULL PRIMARY KEY,
  neighborhood_id  CHAR(36)      NOT NULL,
  street           VARCHAR(191)  NOT NULL,
  house_number     VARCHAR(20)   NOT NULL,
  -- [{x,y}, ...] in image-pixel coordinates, at least 3 points.
  points           JSON          NOT NULL,
  color            CHAR(7)       NOT NULL DEFAULT '#2e7d32',
  added_at         DATETIME      NOT NULL,
  KEY idx_lots_neighborhood (neighborhood_id),
  KEY idx_lots_address (neighborhood_id, street, house_number),
  CONSTRAINT fk_lots_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Membership applications. `universe_member_id` is the Airbridge Universe
-- identity the applicant checked in with before applying (see
-- MembershipService) -- never a freeform typed name alone.
CREATE TABLE IF NOT EXISTS memberships (
  id                   CHAR(36)                              NOT NULL PRIMARY KEY,
  neighborhood_id      CHAR(36)                              NOT NULL,
  universe_member_id   VARCHAR(64)                           NOT NULL,
  name                 VARCHAR(191)                          NOT NULL,
  street               VARCHAR(191)                          NOT NULL,
  house_number         VARCHAR(20)                           NOT NULL,
  unit                 VARCHAR(40)                           NULL,
  status               ENUM('PENDING','APPROVED','DENIED')   NOT NULL DEFAULT 'PENDING',
  eligible              TINYINT(1)                            NOT NULL DEFAULT 0,
  eligibility_reason   VARCHAR(255)                          NOT NULL DEFAULT '',
  requested_at         DATETIME                              NOT NULL,
  decided_at           DATETIME                              NULL,
  decided_by           VARCHAR(64)                           NULL,
  approved_member_id   VARCHAR(64)                           NULL,
  KEY idx_memberships_neighborhood (neighborhood_id),
  KEY idx_memberships_universe_member (universe_member_id),
  CONSTRAINT fk_memberships_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One optional profile per approved address. `address_key` is the same
-- normalized "street|housenumber" key the app computes client- and
-- server-side (see NeighborhoodService::addressKey()).
CREATE TABLE IF NOT EXISTS household_profiles (
  id                     CHAR(36)      NOT NULL PRIMARY KEY,
  neighborhood_id        CHAR(36)      NOT NULL,
  address_key            VARCHAR(255)  NOT NULL,
  street                 VARCHAR(191)  NOT NULL,
  house_number           VARCHAR(20)   NOT NULL,
  -- Homeowner-defined category list for this household, seeded with
  -- ["Family","Pet","Home"] and free to grow -- see ProfileService.
  categories             JSON          NOT NULL,
  email                  VARCHAR(191)  NOT NULL DEFAULT '',
  -- Up to 30 seconds (ProfileService::MAX_INTRO_VIDEO_SECONDS), stored via
  -- ProfileMediaService::storeVideo() -- a /media/... URL, never inline.
  -- All four are NULL together when no video has been added.
  intro_video_path       VARCHAR(500)  NULL,
  intro_video_mime       VARCHAR(60)   NULL,
  intro_video_duration   DECIMAL(5,2)  NULL,
  intro_video_added_at   DATETIME      NULL,
  updated_at             DATETIME      NOT NULL,
  UNIQUE KEY uniq_profile_address (neighborhood_id, address_key),
  CONSTRAINT fk_profiles_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Roster entries, including pets. `phone_number`/`phone_published` are
-- NULL together when no phone is set. `photo_id` optionally links to one
-- of the household's own rows in `profile_photos` (not a foreign key --
-- deliberately nullable/unenforced so removing a photo can't fail a
-- family-member write; ProfileService clears the reference on photo
-- removal instead, mirroring the prototype's original localStorage logic).
CREATE TABLE IF NOT EXISTS family_members (
  id                CHAR(36)              NOT NULL PRIMARY KEY,
  profile_id        CHAR(36)              NOT NULL,
  name              VARCHAR(191)          NOT NULL,
  type              ENUM('PERSON','PET')  NOT NULL DEFAULT 'PERSON',
  photo_id          CHAR(36)              NULL,
  phone_number      VARCHAR(40)           NULL,
  phone_published   TINYINT(1)            NULL,
  created_at        DATETIME              NOT NULL,
  KEY idx_family_profile (profile_id),
  CONSTRAINT fk_family_profile FOREIGN KEY (profile_id)
    REFERENCES household_profiles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Up to 50 per household (enforced in ProfileService, not here).
-- `storage_path` is the public /media/... URL from ProfileMediaService,
-- never a raw data: URL.
CREATE TABLE IF NOT EXISTS profile_photos (
  id             CHAR(36)      NOT NULL PRIMARY KEY,
  profile_id     CHAR(36)      NOT NULL,
  category       VARCHAR(60)   NOT NULL,
  caption        VARCHAR(255)  NOT NULL DEFAULT '',
  storage_path   VARCHAR(500)  NOT NULL,
  added_at       DATETIME      NOT NULL,
  KEY idx_photos_profile (profile_id),
  CONSTRAINT fk_photos_profile FOREIGN KEY (profile_id)
    REFERENCES household_profiles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Messaging: direct message (exactly one other participant), a picked list
-- of several, or a broadcast to everyone approved. `type = 'BROADCAST'`
-- rows still get a `conversation_participants` snapshot at creation time,
-- but MessagingService re-resolves "who can currently see this" from the
-- live approved-member list rather than trusting that snapshot, so a
-- neighbor approved after the broadcast started still sees it.
CREATE TABLE IF NOT EXISTS conversations (
  id                CHAR(36)                          NOT NULL PRIMARY KEY,
  neighborhood_id   CHAR(36)                          NOT NULL,
  type              ENUM('DM','LIST','BROADCAST')     NOT NULL,
  subject           VARCHAR(191)                      NOT NULL DEFAULT '',
  created_by        VARCHAR(64)                       NOT NULL,
  created_at        DATETIME                          NOT NULL,
  last_message_at   DATETIME                          NOT NULL,
  KEY idx_conversations_neighborhood (neighborhood_id, last_message_at),
  CONSTRAINT fk_conversations_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversation_participants (
  conversation_id  CHAR(36)     NOT NULL,
  member_id        VARCHAR(64)  NOT NULL,
  PRIMARY KEY (conversation_id, member_id),
  CONSTRAINT fk_participants_conversation FOREIGN KEY (conversation_id)
    REFERENCES conversations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id                CHAR(36)      NOT NULL PRIMARY KEY,
  conversation_id   CHAR(36)      NOT NULL,
  sender_id         VARCHAR(64)   NOT NULL,
  body              TEXT          NOT NULL,
  sent_at           DATETIME      NOT NULL,
  KEY idx_messages_conversation (conversation_id, sent_at),
  CONSTRAINT fk_messages_conversation FOREIGN KEY (conversation_id)
    REFERENCES conversations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Append-only, mirrors Weddings' realtime_events append-only convention.
CREATE TABLE IF NOT EXISTS audit_log (
  id               CHAR(36)      NOT NULL PRIMARY KEY,
  neighborhood_id  CHAR(36)      NOT NULL,
  action           VARCHAR(80)   NOT NULL,
  actor_id         VARCHAR(64)   NOT NULL,
  details          JSON          NOT NULL,
  at               DATETIME      NOT NULL,
  KEY idx_audit_neighborhood (neighborhood_id, at),
  CONSTRAINT fk_audit_neighborhood FOREIGN KEY (neighborhood_id)
    REFERENCES neighborhoods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- Ceri.us identity/session layer -----------------------------------
-- Same shape as Events/Weddings' weddings_airbridge_identities /
-- weddings_sessions (see that app's server/sql/ceri_identity.sql and
-- CERI_INTEGRATION_AUDIT.md). Dormant: nothing writes here until
-- server/config/ceri.php exists with a real app secret AND Ceri.us has a
-- registered app_id='neighborhoods' row -- see BACKEND_SETUP.md.

CREATE TABLE IF NOT EXISTS airbridge_identities (
  id                  CHAR(36)      NOT NULL PRIMARY KEY,
  airbridge_subject   VARCHAR(255)  NOT NULL,
  created_at          DATETIME      NOT NULL,
  last_login_at       DATETIME      NULL,
  UNIQUE KEY uniq_airbridge_subject (airbridge_subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  selector_hash          CHAR(64)   NOT NULL PRIMARY KEY,
  airbridge_identity_id  CHAR(36)   NOT NULL,
  csrf_hash              CHAR(64)   NOT NULL,
  idle_expires_at        DATETIME   NOT NULL,
  absolute_expires_at    DATETIME   NOT NULL,
  created_at             DATETIME   NOT NULL,
  revoked_at             DATETIME   NULL,
  KEY idx_sessions_identity (airbridge_identity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;