-- =====================================================================
--  Paddlers Cove Community Site - MySQL 8 / MariaDB 10.5+ schema
--  Engine: InnoDB, utf8mb4
--  Run once:  mysql -u USER -p DBNAME < schema.sql
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- USERS + FEDERATED IDENTITIES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email             VARCHAR(255)    NOT NULL,
  display_name      VARCHAR(120)    NOT NULL,
  first_name        VARCHAR(60)     NULL,
  last_name         VARCHAR(60)     NULL,
  avatar_url        VARCHAR(512)    NULL,
  role              ENUM('member','moderator','admin') NOT NULL DEFAULT 'member',
  status            ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  -- Address captured at registration, verified by an admin
  street_number     VARCHAR(20)     NULL,
  street_name       VARCHAR(120)    NULL,
  unit              VARCHAR(30)     NULL,
  city              VARCHAR(80)     NOT NULL DEFAULT 'Lake Wylie',
  state             CHAR(2)         NOT NULL DEFAULT 'SC',
  postal_code       VARCHAR(10)     NULL,
  phone_mobile      VARCHAR(30)     NULL,
  phone_home        VARCHAR(30)     NULL,
  approved_by       BIGINT UNSIGNED NULL,
  approved_at       DATETIME        NULL,
  rejected_reason   VARCHAR(255)    NULL,
  last_login_at     DATETIME        NULL,
  last_login_ip     VARBINARY(16)   NULL,
  created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY ix_users_status (status),
  KEY ix_users_street (street_name),
  CONSTRAINT fk_users_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per (provider, provider_user_id). A single user may link Google + Apple + Facebook.
CREATE TABLE IF NOT EXISTS user_identities (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id           BIGINT UNSIGNED NOT NULL,
  provider          ENUM('google','microsoft','facebook','apple','github','linkedin') NOT NULL,
  provider_user_id  VARCHAR(255)    NOT NULL,
  provider_email    VARCHAR(255)    NULL,
  raw_profile       JSON            NULL,
  created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_identity (provider, provider_user_id),
  KEY ix_identity_user (user_id),
  CONSTRAINT fk_identity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Server-side sessions (so you can force-logout a suspended user instantly)
CREATE TABLE IF NOT EXISTS sessions (
  id            CHAR(64)        NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  ip            VARBINARY(16)   NULL,
  user_agent    VARCHAR(255)    NULL,
  payload       MEDIUMTEXT      NULL,
  last_activity INT UNSIGNED    NOT NULL,
  PRIMARY KEY (id),
  KEY ix_sessions_user (user_id),
  KEY ix_sessions_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- ADDRESS ALLOW-LIST (optional but makes admin approval nearly automatic)
-- Load this with the real plat/street list for the neighborhood.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS community_addresses (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  street_number VARCHAR(20)  NOT NULL,
  street_name   VARCHAR(120) NOT NULL,
  postal_code   VARCHAR(10)  NULL,
  notes         VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_addr (street_number, street_name),
  KEY ix_addr_street (street_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- COMMUNITY DIRECTORY
-- Separate from users so a member can be approved but opt OUT of listing.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS directory_entries (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  is_listed       TINYINT(1)      NOT NULL DEFAULT 1,
  show_address    TINYINT(1)      NOT NULL DEFAULT 1,
  show_phone      TINYINT(1)      NOT NULL DEFAULT 1,
  show_email      TINYINT(1)      NOT NULL DEFAULT 1,
  household_name  VARCHAR(160)    NULL,   -- "The Czaikowski Family"
  bio             TEXT            NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_directory_user (user_id),
  CONSTRAINT fk_directory_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Normalized interests so "Home Theater" is one canonical tag, not 14 spellings
CREATE TABLE IF NOT EXISTS interests (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(80)  NOT NULL,
  slug      VARCHAR(80)  NOT NULL,
  category  VARCHAR(60)  NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_interest_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_interests (
  user_id     BIGINT UNSIGNED NOT NULL,
  interest_id INT UNSIGNED    NOT NULL,
  PRIMARY KEY (user_id, interest_id),
  KEY ix_ui_interest (interest_id),
  CONSTRAINT fk_ui_user     FOREIGN KEY (user_id)     REFERENCES users(id)     ON DELETE CASCADE,
  CONSTRAINT fk_ui_interest FOREIGN KEY (interest_id) REFERENCES interests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Denormalized search blob, rebuilt on save. Makes "advanced search" one fast query.
CREATE TABLE IF NOT EXISTS directory_search_index (
  user_id      BIGINT UNSIGNED NOT NULL,
  search_blob  TEXT            NOT NULL,
  PRIMARY KEY (user_id),
  FULLTEXT KEY ft_directory (search_blob),
  CONSTRAINT fk_dsi_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- LENDING LIBRARY  (the flagship)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS library_categories (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(80)  NOT NULL,
  slug        VARCHAR(80)  NOT NULL,
  icon        VARCHAR(60)  NULL,
  sort_order  SMALLINT     NOT NULL DEFAULT 100,
  PRIMARY KEY (id),
  UNIQUE KEY uq_libcat_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS library_items (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  owner_id        BIGINT UNSIGNED NOT NULL,
  category_id     INT UNSIGNED    NULL,
  kind            ENUM('item','skill','service') NOT NULL DEFAULT 'item',
  title           VARCHAR(160)    NOT NULL,
  description     TEXT            NULL,
  brand_model     VARCHAR(120)    NULL,
  condition_note  VARCHAR(160)    NULL,
  loan_days       SMALLINT        NOT NULL DEFAULT 3,
  deposit_note    VARCHAR(160)    NULL,
  pickup_note     VARCHAR(255)    NULL,
  status          ENUM('available','on_loan','unavailable','retired') NOT NULL DEFAULT 'available',
  is_approved     TINYINT(1)      NOT NULL DEFAULT 1,
  view_count      INT UNSIGNED    NOT NULL DEFAULT 0,
  loan_count      INT UNSIGNED    NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_item_owner (owner_id),
  KEY ix_item_cat (category_id),
  KEY ix_item_status (status),
  FULLTEXT KEY ft_item (title, description, brand_model),
  CONSTRAINT fk_item_owner FOREIGN KEY (owner_id)    REFERENCES users(id)              ON DELETE CASCADE,
  CONSTRAINT fk_item_cat   FOREIGN KEY (category_id) REFERENCES library_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS library_item_photos (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id     BIGINT UNSIGNED NOT NULL,
  file_path   VARCHAR(255)    NOT NULL,
  is_primary  TINYINT(1)      NOT NULL DEFAULT 0,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_photo_item (item_id),
  CONSTRAINT fk_photo_item FOREIGN KEY (item_id) REFERENCES library_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loan_requests (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id       BIGINT UNSIGNED NOT NULL,
  borrower_id   BIGINT UNSIGNED NOT NULL,
  owner_id      BIGINT UNSIGNED NOT NULL,
  status        ENUM('requested','approved','declined','picked_up','returned','cancelled','overdue')
                NOT NULL DEFAULT 'requested',
  message       VARCHAR(500)    NULL,
  requested_for DATE            NULL,
  due_date      DATE            NULL,
  picked_up_at  DATETIME        NULL,
  returned_at   DATETIME        NULL,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_loan_item (item_id),
  KEY ix_loan_borrower (borrower_id),
  KEY ix_loan_owner (owner_id),
  KEY ix_loan_status (status),
  CONSTRAINT fk_loan_item     FOREIGN KEY (item_id)     REFERENCES library_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_loan_borrower FOREIGN KEY (borrower_id) REFERENCES users(id)         ON DELETE CASCADE,
  CONSTRAINT fk_loan_owner    FOREIGN KEY (owner_id)    REFERENCES users(id)         ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- EVENTS CACHE (Google Calendar is the source of truth; this is a cache
-- so the homepage doesn't hit Google's API on every page load)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS event_cache (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  google_id     VARCHAR(255)    NOT NULL,
  title         VARCHAR(255)    NOT NULL,
  description   TEXT            NULL,
  location      VARCHAR(255)    NULL,
  starts_at     DATETIME        NOT NULL,
  ends_at       DATETIME        NULL,
  all_day       TINYINT(1)      NOT NULL DEFAULT 0,
  html_link     VARCHAR(512)    NULL,
  fetched_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_event_google (google_id),
  KEY ix_event_start (starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- RESOURCES (content you'll supply later - trash day, HOA docs, vendors)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resource_categories (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(80)  NOT NULL,
  slug       VARCHAR(80)  NOT NULL,
  sort_order SMALLINT     NOT NULL DEFAULT 100,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rescat_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resources (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id INT UNSIGNED    NULL,
  title       VARCHAR(200)    NOT NULL,
  body        TEXT            NULL,
  url         VARCHAR(512)    NULL,
  phone       VARCHAR(40)     NULL,
  sort_order  SMALLINT        NOT NULL DEFAULT 100,
  is_public   TINYINT(1)      NOT NULL DEFAULT 0,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_res_cat (category_id),
  CONSTRAINT fk_res_cat FOREIGN KEY (category_id) REFERENCES resource_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- AUDIT LOG - who approved whom, who changed what
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_id    BIGINT UNSIGNED NULL,
  action      VARCHAR(80)     NOT NULL,
  entity_type VARCHAR(60)     NULL,
  entity_id   BIGINT UNSIGNED NULL,
  detail      JSON            NULL,
  ip          VARBINARY(16)   NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_audit_actor (actor_id),
  KEY ix_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;