-- ════════════════════════════════════════════════════════
-- fbplease.com — Custom Database Tables
-- Run these against the same MySQL database as the
-- Premium URL Shortener script.
-- ════════════════════════════════════════════════════════

-- ── 1. Properties / feedback activations ──────────────
-- One row per activated feedback link.
-- link_id references the url_db table in the shortener script.

CREATE TABLE IF NOT EXISTS `feedback_properties` (
  `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `link_id`          VARCHAR(100)    NOT NULL,          -- FK to shortener url_db.alias
  `user_id`          INT UNSIGNED    NOT NULL,          -- FK to shortener users.id
  `property_address` VARCHAR(255)    NOT NULL,
  `city_state`       VARCHAR(255)    NOT NULL DEFAULT '',
  `seller_email`     VARCHAR(255)    NOT NULL,
  `agent_email`      VARCHAR(255)    NOT NULL DEFAULT '',
  `four_digit_code`  CHAR(4)         NOT NULL,
  `segment_type`     VARCHAR(50)     NOT NULL DEFAULT 'property',
                                     -- property | inspector | lender | mover | title | pm
  `active`           TINYINT(1)      NOT NULL DEFAULT 1,
  `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_link_id` (`link_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── 2. Feedback submissions ───────────────────────────
-- One row per visitor feedback submission.

CREATE TABLE IF NOT EXISTS `feedback_submissions` (
  `id`                 INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `link_id`            VARCHAR(100)  NOT NULL,
  `submitted_at`       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `star_rating`        TINYINT       NOT NULL DEFAULT 0,
  `price_response`     VARCHAR(50)   NOT NULL DEFAULT '',
  `condition_response` VARCHAR(50)   NOT NULL DEFAULT '',
  `gut_reaction`       VARCHAR(50)   NOT NULL DEFAULT '',
  `lot_response`       VARCHAR(50)   NOT NULL DEFAULT '',
  `location_response`  VARCHAR(50)   NOT NULL DEFAULT '',
  `light_response`     VARCHAR(50)   NOT NULL DEFAULT '',
  `layout_response`    VARCHAR(50)   NOT NULL DEFAULT '',
  `comment`            TEXT                   DEFAULT NULL,
  `contact_optin`      TINYINT(1)    NOT NULL DEFAULT 0,
  `notified_at`        DATETIME               DEFAULT NULL, -- when email was sent
  PRIMARY KEY (`id`),
  KEY `idx_link_id`    (`link_id`),
  KEY `idx_submitted`  (`submitted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── 3. Drawing entries ────────────────────────────────
-- Visitor name/email collected after feedback submission.
-- Separate from submissions so feedback stays anonymous
-- unless visitor opts in to drawing.

CREATE TABLE IF NOT EXISTS `drawing_entries` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `submission_id` INT UNSIGNED  NOT NULL,
  `link_id`       VARCHAR(100)  NOT NULL,
  `month_year`    CHAR(7)       NOT NULL,  -- e.g. 2026-01
  `segment_type`  VARCHAR(50)   NOT NULL DEFAULT 'property',
  `visitor_name`  VARCHAR(255)  NOT NULL,
  `visitor_email` VARCHAR(255)  NOT NULL,
  `winner`        TINYINT(1)    NOT NULL DEFAULT 0,
  `notified_at`   DATETIME               DEFAULT NULL,
  `created_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_month`   (`month_year`),
  KEY `idx_segment` (`segment_type`),
  KEY `idx_winner`  (`winner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── 4. Drawing pool ───────────────────────────────────
-- Tracks the prize amount per segment per month.
-- Admin updates this as enrollments grow.

CREATE TABLE IF NOT EXISTS `drawing_pools` (
  `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `month_year`   CHAR(7)       NOT NULL,  -- e.g. 2026-01
  `segment_type` VARCHAR(50)   NOT NULL DEFAULT 'property',
  `pool_amount`  DECIMAL(8,2)  NOT NULL DEFAULT 0.00,
  `sponsor_name` VARCHAR(255)  NOT NULL DEFAULT '',
  `drawing_date` DATE                   DEFAULT NULL,
  `drawn`        TINYINT(1)    NOT NULL DEFAULT 0,
  `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_month_segment` (`month_year`, `segment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ════════════════════════════════════════════════════════
-- Sample data for testing
-- ════════════════════════════════════════════════════════

INSERT INTO `feedback_properties`
  (link_id, user_id, property_address, city_state, seller_email, agent_email, four_digit_code, segment_type)
VALUES
  ('abc123', 1, '142 Birchwood Drive', 'Concord, NH 03301', 'seller@example.com', 'agent@brokerage.com', '4827', 'property'),
  ('def456', 1, '88 Maple Ridge Road', 'Bow, NH 03304',     'seller2@example.com', '',                   '3916', 'property');

INSERT INTO `drawing_pools`
  (month_year, segment_type, pool_amount, sponsor_name, drawing_date)
VALUES
  ('2026-01', 'property', 175.00, 'First NH Mortgage', '2026-01-31'),
  ('2026-01', 'inspector', 80.00, 'NH Home Warranty Co.', '2026-01-31');