GroundShareDB.sql

-- ============================================================
-- GroundShareDB — Full Schema + Stored Procedures
-- Single source of truth. Run once on a fresh database.
-- Safe to re-run individual SP blocks (CREATE OR ALTER).
-- ============================================================
--
-- CHANGELOG — cleanup pass (re-run these procs on PROD in SSMS):
--   * sp_GetEventsByLocation  — dropped redundant EventType / VoteCount aliases
--                               (canonical: EventType_Name, Vote_Count)
--   * sp_VoteOnEvent          — dropped redundant VoteCount alias (canonical: Vote_Count)
--   * sp_ToggleFavorite       — SERIALIZABLE txn closes check-then-write race
--   * sp_ToggleNotification   — SERIALIZABLE txn closes check-then-write race
--   * sp_UpsertGoogleUser     — Last_Active now uses SYSUTCDATETIME (was local GETDATE)
-- ============================================================

IF DB_ID('GroundShareDB') IS NULL
    CREATE DATABASE GroundShareDB;
GO

USE GroundShareDB;
GO

-- Required for INSERT into tables with filtered/computed indexes or indexed views.
-- Stored procedures bake in these SET options at CREATE time.
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

-- ============================================================
-- TABLES
-- ============================================================

-- ---- Lookup tables ----

IF OBJECT_ID('dbo.city', 'U') IS NULL
CREATE TABLE city (
    City_ID   INT IDENTITY(1,1) PRIMARY KEY,
    City_Name NVARCHAR(255) NOT NULL CHECK (LEN(TRIM(City_Name)) > 0),
    District  NVARCHAR(255)
);
GO

IF OBJECT_ID('dbo.event_type', 'U') IS NULL
CREATE TABLE event_type (
    EventType_ID INT IDENTITY(1,1) PRIMARY KEY,
    Name         NVARCHAR(255) NOT NULL UNIQUE CHECK (LEN(TRIM(Name)) > 0)
);
GO

-- ---- Streets ----

IF OBJECT_ID('dbo.streets', 'U') IS NULL
CREATE TABLE streets (
    Street_ID   INT IDENTITY(1,1) PRIMARY KEY,
    City_ID     INT NOT NULL REFERENCES city(City_ID),
    Street_Name NVARCHAR(255) NOT NULL CHECK (LEN(TRIM(Street_Name)) > 0),
    UNIQUE (City_ID, Street_Name)
);
GO

-- ---- Location ----

IF OBJECT_ID('dbo.location', 'U') IS NULL
CREATE TABLE location (
    Location_ID    INT IDENTITY(1,1) PRIMARY KEY,
    City_ID        INT NOT NULL REFERENCES city(City_ID),
    Street_ID      INT REFERENCES streets(Street_ID),
    House_Number   NVARCHAR(50),
    Block_Parcel   NVARCHAR(100),
    Zoning_Purpose NVARCHAR(255),
    Latitude       FLOAT,
    Longitude      FLOAT,
    -- ── Canonical address identity ──────────────────────────────────────────
    -- TlvAddressId is the Tel Aviv-Yafo GIS address id (id_ktovet, layer 527):
    -- a STABLE, DISCRETE id per house number — the one true identity for an
    -- addressed point. It replaces the old coordinate-grid GeoKey as the primary
    -- key because a grid is fundamentally wrong for addresses: a ~11m cell
    -- over-merges adjacent house numbers (46/48 land in one cell) AND still
    -- boundary-splits the same building (a few meters across a cell edge → two
    -- cells). id_ktovet has neither failure mode. Gush/Chelka = cadastral
    -- block/parcel for that address (display + future map-overlay use).
    TlvAddressId   INT,
    Gush           INT,
    Chelka         INT,
    -- GeoKey is now only a FALLBACK identity for coord-bearing rows OUTSIDE TLV
    -- coverage (other cities) or unaddressed points — TLV rows key on
    -- TlvAddressId and are excluded from the GeoKey unique index. Snapped to
    -- DECIMAL(9,5) ≈ 1m (NOT a raw FLOAT→string convert, which can emit
    -- scientific notation and break cell equality). The pin-snap (reverse→
    -- forward geocode) makes the same address converge to one Google point, so a
    -- ~1m cell no longer fragments. NULL when the row has no coords (text-only
    -- rows resolve by the City/Street/House triple). PERSISTED so a UNIQUE index
    -- can sit on it. The ALTER below and sp_ResolveLocationCanonical's @GeoKey
    -- MUST use this exact same expression.
    GeoKey AS (
        CASE WHEN Latitude IS NOT NULL AND Longitude IS NOT NULL
             THEN CONCAT(CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), Latitude)), ',',
                         CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), Longitude)))
        END
    ) PERSISTED
);
GO

-- Additive columns for databases where `location` already exists (the CREATE
-- TABLE above is skipped on a non-fresh DB). Mirrors the ALTER-if-missing
-- pattern used for refresh_token / event columns. NOTE: the GeoKey precision
-- change (9,4 → 9,5) on a pre-existing DB is handled by ResetData.sql (it drops
-- and re-adds the column on the emptied table) — the guard below only ADDS a
-- missing column, it never alters an existing one.
IF COL_LENGTH('dbo.location', 'TlvAddressId') IS NULL
    ALTER TABLE location ADD TlvAddressId INT;
GO
IF COL_LENGTH('dbo.location', 'Gush') IS NULL
    ALTER TABLE location ADD Gush INT;
GO
IF COL_LENGTH('dbo.location', 'Chelka') IS NULL
    ALTER TABLE location ADD Chelka INT;
GO
-- The CASE/DECIMAL(9,5) expression MUST stay identical to the CREATE TABLE
-- definition and to sp_ResolveLocationCanonical's @GeoKey. Own batch BEFORE the
-- unique index so the index compiles against an existing column.
IF COL_LENGTH('dbo.location', 'GeoKey') IS NULL
    ALTER TABLE location ADD GeoKey AS (
        CASE WHEN Latitude IS NOT NULL AND Longitude IS NOT NULL
             THEN CONCAT(CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), Latitude)), ',',
                         CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), Longitude)))
        END
    ) PERSISTED;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_location_geo')
    CREATE INDEX IX_location_geo ON location(Latitude, Longitude) WHERE Latitude IS NOT NULL;
GO

-- One TLV address (id_ktovet) = one row. Filtered UNIQUE index makes a duplicate
-- physically impossible AND is load-bearing for concurrency: a racing duplicate
-- INSERT throws here and sp_ResolveLocationCanonical catches it / re-reads.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UX_location_tlvaddr')
    CREATE UNIQUE INDEX UX_location_tlvaddr ON location(TlvAddressId)
        WHERE TlvAddressId IS NOT NULL;
GO

-- One physical address = one row (fallback path). Filtered UNIQUE index over
-- coord-bearing NON-TLV rows only — TLV rows (TlvAddressId NOT NULL) key on the
-- index above and are excluded here, and text-only rows (NULL coords → NULL
-- GeoKey) are exempt. The filter uses the BASE columns, not GeoKey — SQL Server
-- forbids a computed column in a filtered-index predicate (Msg 10609).
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UX_location_geokey')
    CREATE UNIQUE INDEX UX_location_geokey ON location(GeoKey)
        WHERE Latitude IS NOT NULL AND Longitude IS NOT NULL AND TlvAddressId IS NULL;
GO

-- ---- Users ----

IF OBJECT_ID('dbo.[user]', 'U') IS NULL
CREATE TABLE [user] (
    User_ID         INT IDENTITY(1,1) PRIMARY KEY,
    Email           NVARCHAR(255) NOT NULL UNIQUE CHECK (Email LIKE '%_@__%.__%'),
    Password_Hash   NVARCHAR(255) NOT NULL,
    Full_Name       NVARCHAR(255) NOT NULL,
    Phone           NVARCHAR(50) CHECK (Phone NOT LIKE '%[^0-9+() -]%'),
    User_Address    INT REFERENCES location(Location_ID),
    XP              INT NOT NULL DEFAULT 0,
    Streak_Days     INT NOT NULL DEFAULT 0,
    Last_Active     DATE DEFAULT GETDATE(),
    User_Role       NVARCHAR(100) DEFAULT N'תושב חדש',
    User_Reason     NVARCHAR(255),
    Onboarding_Done BIT NOT NULL DEFAULT 0,
    Created_At      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Is_Active       BIT NOT NULL DEFAULT 1
);
GO

-- ---- Refresh tokens ----

IF OBJECT_ID('dbo.refresh_token', 'U') IS NULL
CREATE TABLE refresh_token (
    Token_ID   INT IDENTITY(1,1) PRIMARY KEY,
    User_ID    INT NOT NULL REFERENCES [user](User_ID),
    Token      NVARCHAR(500) NOT NULL UNIQUE,
    Expires_At DATETIME2 NOT NULL,
    Created_At DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Revoked_At DATETIME2
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_refresh_token_user')
    CREATE INDEX IX_refresh_token_user ON refresh_token(User_ID);
GO

-- Token rotation columns (additive — safe to re-run).
--   Replaced_By : the hash of the successor token this row was rotated into.
--                 Lets a racing concurrent refresh (same token, within the grace
--                 window) receive the already-issued successor instead of a 401.
--   Reused_At   : set when a token that was ALREADY rotated is presented again
--                 outside the grace window — a token-reuse (theft) signal that
--                 triggers revocation of the whole token family.
IF COL_LENGTH('dbo.refresh_token', 'Replaced_By') IS NULL
    ALTER TABLE refresh_token ADD Replaced_By NVARCHAR(500) NULL;
GO
IF COL_LENGTH('dbo.refresh_token', 'Reused_At') IS NULL
    ALTER TABLE refresh_token ADD Reused_At DATETIME2 NULL;
GO

-- Lookup by token hash is the hot path on every refresh; back it with an index.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_refresh_token_token')
    CREATE INDEX IX_refresh_token_token ON refresh_token(Token);
GO

-- ---- Password reset tokens (stored hashed — SHA-256 hex, done in C# before calling SP) ----

IF OBJECT_ID('dbo.password_reset_token', 'U') IS NULL
CREATE TABLE password_reset_token (
    Token_ID    INT IDENTITY(1,1) PRIMARY KEY,
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Token_Hash  CHAR(64) NOT NULL UNIQUE,
    Expires_At  DATETIME2 NOT NULL,
    Used_At     DATETIME2 NULL,
    Created_At  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Created_IP  NVARCHAR(45) NULL
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_password_reset_token_user')
    CREATE INDEX IX_password_reset_token_user ON password_reset_token(User_ID);
GO

-- ---- Events / Reports ----

IF OBJECT_ID('dbo.event', 'U') IS NULL
CREATE TABLE event (
    Event_ID     INT IDENTITY(1,1) PRIMARY KEY,
    Location_ID  INT NOT NULL REFERENCES location(Location_ID),
    EventType_ID INT NOT NULL REFERENCES event_type(EventType_ID),
    User_ID      INT NOT NULL REFERENCES [user](User_ID),
    Start_Date   DATETIME NOT NULL,
    End_Date     DATETIME,
    Description  NVARCHAR(MAX),
    EventStatus  NVARCHAR(100) NOT NULL DEFAULT N'קורה'
        CHECK (EventStatus IN (N'קורה', N'קרה', N'עתיד לקרות')),
    Picture      NVARCHAR(MAX),
    Created_At   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CHECK (End_Date IS NULL OR End_Date >= Start_Date)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_event_location')
    CREATE INDEX IX_event_location ON event(Location_ID);
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_event_user')
    CREATE INDEX IX_event_user ON event(User_ID);
GO

-- Category filters/joins (event_type lookups on nearby + location feeds).
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_event_eventtype')
    CREATE INDEX IX_event_eventtype ON event(EventType_ID);
GO

-- 3-day relevance confirmation columns. Declared here (next to the table)
-- rather than in a trailing migration block so they exist before the read
-- SPs below reference them.
IF COL_LENGTH('dbo.event', 'Last_Confirmation_Prompt_At') IS NULL
    ALTER TABLE event ADD Last_Confirmation_Prompt_At DATETIME2 NULL;
GO
IF COL_LENGTH('dbo.event', 'Last_Confirmation_Response') IS NULL
    ALTER TABLE event ADD Last_Confirmation_Response NVARCHAR(20) NULL;
GO
IF COL_LENGTH('dbo.event', 'Consecutive_Snooze_Count') IS NULL
    ALTER TABLE event ADD Consecutive_Snooze_Count INT NOT NULL CONSTRAINT DF_event_snooze_count DEFAULT 0;
GO
IF COL_LENGTH('dbo.event', 'Is_Closed') IS NULL
    ALTER TABLE event ADD Is_Closed BIT NOT NULL CONSTRAINT DF_event_is_closed DEFAULT 0;
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_event_relevance_check')
    CREATE INDEX IX_event_relevance_check ON event(Is_Closed, Created_At) INCLUDE (User_ID, End_Date, Last_Confirmation_Prompt_At);
GO

-- ---- Event votes ----

IF OBJECT_ID('dbo.event_vote', 'U') IS NULL
CREATE TABLE event_vote (
    User_ID  INT NOT NULL REFERENCES [user](User_ID),
    Event_ID INT NOT NULL REFERENCES event(Event_ID),
    Vote     SMALLINT NOT NULL CHECK (Vote IN (-1, 1)),
    Voted_At DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (User_ID, Event_ID)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_event_vote_event')
    CREATE INDEX IX_event_vote_event ON event_vote(Event_ID);
GO

-- ---- Comments ----

IF OBJECT_ID('dbo.comment', 'U') IS NULL
CREATE TABLE comment (
    Comment_ID INT IDENTITY(1,1) PRIMARY KEY,
    User_ID    INT NOT NULL REFERENCES [user](User_ID),
    Event_ID   INT NOT NULL REFERENCES event(Event_ID),
    Text       NVARCHAR(MAX) NOT NULL,
    Picture    NVARCHAR(MAX),
    Created_At DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_comment_event')
    CREATE INDEX IX_comment_event ON comment(Event_ID);
GO

-- "Comments written by user X" (activity counts, profile stats).
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_comment_user')
    CREATE INDEX IX_comment_user ON comment(User_ID);
GO

-- ---- Reviews ----

IF OBJECT_ID('dbo.user_review', 'U') IS NULL
CREATE TABLE user_review (
    Review_ID   INT IDENTITY(1,1) PRIMARY KEY,
    Location_ID INT NOT NULL REFERENCES location(Location_ID),
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Text        NVARCHAR(MAX),
    Rating      INT NOT NULL CHECK (Rating BETWEEN 1 AND 5),
    Is_Positive BIT NOT NULL DEFAULT 1,
    Picture     NVARCHAR(MAX),
    Created_At  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_review_location')
    CREATE INDEX IX_review_location ON user_review(Location_ID);
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UQ_review_user_location')
    CREATE UNIQUE INDEX UQ_review_user_location ON user_review(User_ID, Location_ID);
GO

-- ---- Favorites ----

IF OBJECT_ID('dbo.add_to_favorites', 'U') IS NULL
CREATE TABLE add_to_favorites (
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Location_ID INT NOT NULL REFERENCES location(Location_ID),
    Added_Date  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (User_ID, Location_ID)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_favorites_location')
    CREATE INDEX IX_favorites_location ON add_to_favorites(Location_ID);
GO

-- ---- Plan status (building permits) ----

IF OBJECT_ID('dbo.PlanStatus', 'U') IS NULL
CREATE TABLE PlanStatus (
    Plan_ID          INT IDENTITY(1,1) PRIMARY KEY,
    Location_ID      INT NOT NULL REFERENCES location(Location_ID),
    Work_Type        NVARCHAR(255),
    Work_Scope       NVARCHAR(MAX),
    Request_Essence  NVARCHAR(MAX),
    Start_Date       DATETIME NOT NULL,
    End_Date         DATETIME,
    Current_Stage    NVARCHAR(255) DEFAULT N'Initiated',
    CHECK (End_Date IS NULL OR End_Date >= Start_Date)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_planstatus_location')
    CREATE INDEX IX_planstatus_location ON PlanStatus(Location_ID);
GO

-- ---- Comparison history ----

IF OBJECT_ID('dbo.comparison', 'U') IS NULL
CREATE TABLE comparison (
    Comparison_ID INT IDENTITY(1,1) PRIMARY KEY,
    User_ID       INT NOT NULL REFERENCES [user](User_ID),
    Created_At    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- sp_GetComparisonHistory filters by User_ID, newest first.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_comparison_user')
    CREATE INDEX IX_comparison_user ON comparison(User_ID, Created_At DESC);
GO

IF OBJECT_ID('dbo.comparison_address', 'U') IS NULL
CREATE TABLE comparison_address (
    Comparison_ID INT NOT NULL REFERENCES comparison(Comparison_ID),
    Location_ID   INT NOT NULL REFERENCES location(Location_ID),
    Score         INT,
    Is_Winner     BIT NOT NULL DEFAULT 0,
    Address       NVARCHAR(500),
    PRIMARY KEY (Comparison_ID, Location_ID)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_comparison_addr_location')
    CREATE INDEX IX_comparison_addr_location ON comparison_address(Location_ID);
GO

-- ---- Onboarding / survey ----

IF OBJECT_ID('dbo.survey', 'U') IS NULL
CREATE TABLE survey (
    Survey_ID            INT IDENTITY(1,1) PRIMARY KEY,
    User_ID              INT NOT NULL UNIQUE REFERENCES [user](User_ID),
    Is_Renter            BIT NOT NULL DEFAULT 0,
    Is_Owner             BIT NOT NULL DEFAULT 0,
    Is_LivingWithParents BIT NOT NULL DEFAULT 0,
    Is_Investor          BIT NOT NULL DEFAULT 0,
    Is_StatusOther       BIT NOT NULL DEFAULT 0,
    Is_LookingForApartment BIT NOT NULL DEFAULT 0,
    Is_Resident          BIT NOT NULL DEFAULT 0,
    Is_RealEstateInvestor BIT NOT NULL DEFAULT 0,
    Is_Professional      BIT NOT NULL DEFAULT 0,
    Is_GovRepresentative BIT NOT NULL DEFAULT 0,
    Created_At           DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- ---- User activity log ----

IF OBJECT_ID('dbo.user_activity', 'U') IS NULL
CREATE TABLE user_activity (
    Activity_ID INT IDENTITY(1,1) PRIMARY KEY,
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Action_Type NVARCHAR(50) NOT NULL,
    Description NVARCHAR(500) NOT NULL,
    XP_Earned   INT NOT NULL DEFAULT 0,
    Location_ID INT NULL REFERENCES location(Location_ID),
    Created_At  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_activity_user')
    CREATE INDEX IX_activity_user ON user_activity(User_ID, Created_At DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.user_activity') AND name = 'Location_ID')
    ALTER TABLE user_activity ADD Location_ID INT NULL REFERENCES location(Location_ID);
GO

-- ---- Earned achievement badges (permanent — "once earned, always earned") ----
-- One row per (user, badge) the moment its threshold is first met. Rows are
-- never deleted, so a badge stays earned even if the underlying stat later
-- drops (a streak resets, an upvote is withdrawn, etc.). Badge_ID values match
-- the ids in computeBadges() in
-- 03-client/src/app/components/personal/lib/levelSystem.ts.
IF OBJECT_ID('dbo.user_badge', 'U') IS NULL
CREATE TABLE user_badge (
    User_ID   INT NOT NULL REFERENCES [user](User_ID),
    Badge_ID  NVARCHAR(40) NOT NULL,
    Earned_At DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (User_ID, Badge_ID)
);
GO

-- ---- Weekly mission grants ----
-- One row per (user, ISO week number, mission id). Prevents double-claiming.
-- Week_Number = DATEPART(ISO_WEEK, GETUTCDATE()) * 100 + YEAR so it resets
-- each calendar year.

IF OBJECT_ID('dbo.user_mission_grant', 'U') IS NULL
CREATE TABLE user_mission_grant (
    Grant_ID    INT IDENTITY(1,1) PRIMARY KEY,
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Mission_ID  NVARCHAR(40) NOT NULL,
    Week_Key    INT NOT NULL,   -- YYYY * 100 + ISO_WEEK, e.g. 202623
    XP_Awarded  INT NOT NULL,
    Granted_At  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UNIQUE (User_ID, Mission_ID, Week_Key)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_mission_grant_user')
    CREATE INDEX IX_mission_grant_user ON user_mission_grant(User_ID, Week_Key);
GO

-- ---- Notification subscriptions ----

IF OBJECT_ID('dbo.notification_subscription', 'U') IS NULL
CREATE TABLE notification_subscription (
    User_ID     INT NOT NULL REFERENCES [user](User_ID),
    Location_ID INT NOT NULL REFERENCES location(Location_ID),
    Created_At  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (User_ID, Location_ID)
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_notification_sub_location')
    CREATE INDEX IX_notification_sub_location ON notification_subscription(Location_ID);
GO

-- ---- GPS proximity notification subscriptions ----
-- One row per user. Tracks the most recent GPS sample the client pushed while
-- the user has the "דיווחים בקרבתי" toggle enabled. Used to fan out push when
-- a new event is created within radius of any of these coordinates.

IF OBJECT_ID('dbo.gps_notification_subscription', 'U') IS NULL
CREATE TABLE gps_notification_subscription (
    User_ID    INT NOT NULL PRIMARY KEY,
    Last_Lat   FLOAT NOT NULL,
    Last_Lng   FLOAT NOT NULL,
    Is_Enabled BIT NOT NULL DEFAULT 1,
    Updated_At DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_gps_notif_user FOREIGN KEY (User_ID) REFERENCES [user](User_ID) ON DELETE CASCADE
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_gps_notif_active')
    CREATE INDEX IX_gps_notif_active ON gps_notification_subscription(Is_Enabled, Updated_At);
GO

-- ---- Device tokens (FCM push) ----

IF OBJECT_ID('dbo.device_token', 'U') IS NULL
CREATE TABLE device_token (
    Device_Token_ID INT IDENTITY(1,1) PRIMARY KEY,
    User_ID         INT NOT NULL,
    Token           NVARCHAR(500) NOT NULL,
    Platform        NVARCHAR(10) NOT NULL,
    Created_At      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Last_Seen_At    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_device_token_user  FOREIGN KEY (User_ID) REFERENCES [user](User_ID) ON DELETE CASCADE,
    CONSTRAINT UQ_device_token_token UNIQUE (Token),
    CONSTRAINT CK_device_token_platform CHECK (Platform IN ('ios', 'android', 'web'))
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_device_token_user')
    CREATE INDEX IX_device_token_user ON device_token(User_ID);
GO

-- ---- In-app notification feed ----
-- A persistent, per-user feed (distinct from ephemeral FCM push). Each row is
-- one notification the recipient can open, mark seen, or dismiss. Today the
-- only Type is 'comment' (someone commented on the recipient's report), but the
-- shape is generic so future types can reuse it.
IF OBJECT_ID('dbo.user_notification', 'U') IS NULL
CREATE TABLE user_notification (
    Notification_ID   INT IDENTITY(1,1) PRIMARY KEY,
    Recipient_User_ID INT NOT NULL REFERENCES [user](User_ID),
    Type              NVARCHAR(40) NOT NULL,
    Event_ID          INT NULL REFERENCES [event](Event_ID),
    Actor_User_ID     INT NULL REFERENCES [user](User_ID),
    Comment_ID        INT NULL REFERENCES comment(Comment_ID),
    Created_At        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Seen_At           DATETIME2 NULL,
    Dismissed_At      DATETIME2 NULL
);
GO

-- Feed read path: recipient's active (non-dismissed) rows, newest first.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_user_notification_recipient')
    CREATE INDEX IX_user_notification_recipient
        ON user_notification(Recipient_User_ID, Dismissed_At, Created_At DESC);
GO

-- FK columns referencing event / actor / comment. The feed's LEFT JOINs hit
-- the target PKs, but every DELETE on event/comment/user must verify no
-- referencing rows exist here — without these indexes that check is a full
-- scan of user_notification, which grows with every comment notification.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_user_notification_event')
    CREATE INDEX IX_user_notification_event ON user_notification(Event_ID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_user_notification_actor')
    CREATE INDEX IX_user_notification_actor ON user_notification(Actor_User_ID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_user_notification_comment')
    CREATE INDEX IX_user_notification_comment ON user_notification(Comment_ID);
GO

-- ---- Security audit trail (OWASP A09) ----
-- Written by AuditService via sp_InsertAuditLog: login success/failure,
-- registration, account deletion, password change, home address change,
-- and admin actions. User_ID is nullable (failed logins have no user) and
-- deliberately has NO foreign key — the trail must survive account deletion
-- for post-incident forensics.
IF OBJECT_ID('dbo.audit_log', 'U') IS NULL
CREATE TABLE audit_log (
    Audit_ID      BIGINT IDENTITY(1,1) PRIMARY KEY,
    User_ID       INT NULL,
    Action        NVARCHAR(100) NOT NULL,
    IP_Address    NVARCHAR(45) NULL,      -- fits IPv6
    User_Agent    NVARCHAR(400) NULL,
    Metadata_JSON NVARCHAR(MAX) NULL,
    Created_At    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- Forensics read paths: "what did user X do" and "what happened lately".
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_audit_log_user')
    CREATE INDEX IX_audit_log_user ON audit_log(User_ID, Created_At DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_audit_log_created')
    CREATE INDEX IX_audit_log_created ON audit_log(Created_At DESC);
GO

-- ============================================================
-- SEED DATA
-- ============================================================

IF NOT EXISTS (SELECT 1 FROM event_type)
INSERT INTO event_type (Name) VALUES
    (N'מפגע שכונתי / פגיעה באיכות חיים'),
    (N'שיפוץ / בנייה'),
    (N'תחבורה / כבישים'),
    (N'ניקיון / תברואה'),
    (N'ביטחון / בטיחות'),
    (N'אחר');
GO

-- ============================================================
-- HELPER UDF  (address normalization)
-- ============================================================
-- Normalizes city/street names: trim whitespace, collapse runs of spaces,
-- remove spaces around hyphens. Used by all write-path SPs.

IF OBJECT_ID('dbo.fn_NormalizeAddressPart', 'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_NormalizeAddressPart;
GO

CREATE FUNCTION dbo.fn_NormalizeAddressPart(@s NVARCHAR(255))
RETURNS NVARCHAR(255)
WITH SCHEMABINDING
AS
BEGIN
    IF @s IS NULL RETURN NULL;
    DECLARE @t NVARCHAR(255) = LTRIM(RTRIM(@s));
    WHILE CHARINDEX('  ', @t) > 0
        SET @t = REPLACE(@t, '  ', ' ');
    SET @t = REPLACE(@t, ' - ', '-');
    SET @t = REPLACE(@t, ' -',  '-');
    SET @t = REPLACE(@t, '- ',  '-');
    RETURN @t;
END
GO

-- ============================================================
-- STORED PROCEDURES
-- ============================================================

-- ---- Internal helper: resolve (or create) city + street IDs from raw names ----
-- Normalizes inputs and upserts into the city/streets lookup tables.
-- Returns the resolved IDs via OUTPUT params. Safe to call inside a caller-owned transaction.
CREATE OR ALTER PROCEDURE sp_ResolveCityStreet
    @City_Name   NVARCHAR(255),
    @Street_Name NVARCHAR(255) = NULL,
    @CityID      INT OUTPUT,
    @StreetID    INT OUTPUT,
    @NormCity    NVARCHAR(255) OUTPUT,
    @NormStreet  NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @NormCity   = dbo.fn_NormalizeAddressPart(@City_Name);
    SET @NormStreet = dbo.fn_NormalizeAddressPart(@Street_Name);
    SET @StreetID   = NULL;

    SELECT @CityID = City_ID FROM city WHERE City_Name = @NormCity;
    IF @CityID IS NULL
    BEGIN
        INSERT INTO city (City_Name) VALUES (@NormCity);
        SET @CityID = SCOPE_IDENTITY();
    END

    IF @NormStreet IS NOT NULL AND LEN(@NormStreet) > 0
    BEGIN
        SELECT @StreetID = Street_ID FROM streets WHERE City_ID = @CityID AND Street_Name = @NormStreet;
        IF @StreetID IS NULL
        BEGIN
            INSERT INTO streets (City_ID, Street_Name) VALUES (@CityID, @NormStreet);
            SET @StreetID = SCOPE_IDENTITY();
        END
    END
END
GO

-- ---------------------------------------------------------------------------
-- sp_ResolveLocationCanonical — THE single canonical address→Location_ID path.
-- ---------------------------------------------------------------------------
-- One physical address must map to exactly one `location` row. Identity is a
-- DISCRETE real-world id, never a rounded coordinate:
--
--   1. @TlvAddressId (id_ktovet, Tel Aviv-Yafo GIS layer 527) — primary key for
--      any addressed point in TLV. Stable per house number; resolved server-side
--      from coords in C# (TlvAddressService) and passed in. This is what fixes
--      both the old grid pathologies at once: a ~11m GeoKey cell over-merged
--      adjacent numbers (46≡48) AND boundary-split the same building.
--   2. GeoKey (DECIMAL(9,5) ≈ 1m) — fallback for coord-bearing rows OUTSIDE TLV
--      coverage / unaddressed points. The pin-snap converges a given address to
--      one Google point, so a ~1m cell no longer fragments it.
--   3. Text triple (City/Street/House) — last resort when no coords (NULL GeoKey,
--      exempt from the unique indexes; unified the first time a coord/TLV resolve
--      hits the same address via the adopt steps below).
--
-- Adoption: a row created by a lower-precedence path (text-only, or a non-TLV
-- coord row from a transient GIS outage) is UPGRADED to the canonical
-- @TlvAddressId on the next successful resolve, rather than duplicated.
--
-- Concurrency: two racing resolves of a new address both miss the lookup and
-- both INSERT; the filtered UNIQUE index (UX_location_tlvaddr or UX_location_
-- geokey) makes the second INSERT fail (2601/2627), which we catch and re-read.
-- That's why those unique indexes are load-bearing, not cosmetic.
-- ---------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE sp_ResolveLocationCanonical
    @City_Name    NVARCHAR(255),
    @Street_Name  NVARCHAR(255) = NULL,
    @House_Number NVARCHAR(50)  = NULL,
    @Lat          FLOAT         = NULL,
    @Lng          FLOAT         = NULL,
    @TlvAddressId INT           = NULL,
    @Gush         INT           = NULL,
    @Chelka       INT           = NULL,
    @Location_ID  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NormHouse NVARCHAR(50) = NULLIF(LTRIM(RTRIM(@House_Number)), '');
    DECLARE @CityID INT, @StreetID INT;
    DECLARE @NormCity NVARCHAR(255), @NormStreet NVARCHAR(255);
    EXEC sp_ResolveCityStreet @City_Name, @Street_Name,
         @CityID OUTPUT, @StreetID OUTPUT, @NormCity OUTPUT, @NormStreet OUTPUT;

    SET @Location_ID = NULL;

    -- GeoKey for the supplied coords (DECIMAL(9,5) — MUST match the location.GeoKey
    -- computed-column expression exactly or lookups/adoption won't find the cell).
    DECLARE @GeoKey NVARCHAR(41) = NULL;
    IF @Lat IS NOT NULL AND @Lng IS NOT NULL
        SET @GeoKey = CONCAT(CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), @Lat)), ',',
                             CONVERT(NVARCHAR(20), CONVERT(DECIMAL(9,5), @Lng)));

    -- ===== 1. Canonical TLV address identity (id_ktovet) =====
    IF @TlvAddressId IS NOT NULL
    BEGIN
        SELECT @Location_ID = Location_ID FROM location WHERE TlvAddressId = @TlvAddressId;

        IF @Location_ID IS NOT NULL
        BEGIN
            -- Backfill descriptive/coord/cadastral fields; never overwrite
            -- existing non-blank values.
            UPDATE location
            SET Street_ID    = COALESCE(Street_ID, @StreetID),
                House_Number = COALESCE(NULLIF(House_Number, N''), @NormHouse),
                Latitude     = COALESCE(Latitude, @Lat),
                Longitude    = COALESCE(Longitude, @Lng),
                Gush         = COALESCE(Gush, @Gush),
                Chelka       = COALESCE(Chelka, @Chelka)
            WHERE Location_ID = @Location_ID;
            RETURN;
        END

        -- Adopt a same-address coord row that was created before TLV resolution
        -- succeeded (e.g. a GIS outage took the non-TLV fallback) and stamp it
        -- with the canonical id, so the address unifies into one row.
        IF @GeoKey IS NOT NULL
        BEGIN
            UPDATE TOP (1) location
            SET TlvAddressId = @TlvAddressId,
                Gush         = COALESCE(Gush, @Gush),
                Chelka       = COALESCE(Chelka, @Chelka),
                Street_ID    = COALESCE(Street_ID, @StreetID),
                House_Number = COALESCE(NULLIF(House_Number, N''), @NormHouse)
            WHERE TlvAddressId IS NULL AND GeoKey = @GeoKey;

            IF @@ROWCOUNT > 0
            BEGIN
                SELECT @Location_ID = Location_ID FROM location WHERE TlvAddressId = @TlvAddressId;
                RETURN;
            END
        END

        -- Adopt a coord-less text-triple row (e.g. created at registration).
        UPDATE TOP (1) location
        SET TlvAddressId = @TlvAddressId,
            Latitude     = COALESCE(Latitude, @Lat),
            Longitude    = COALESCE(Longitude, @Lng),
            Gush         = COALESCE(Gush, @Gush),
            Chelka       = COALESCE(Chelka, @Chelka)
        WHERE TlvAddressId IS NULL AND Latitude IS NULL AND Longitude IS NULL
          AND City_ID = @CityID
          AND (Street_ID = @StreetID OR (Street_ID IS NULL AND @StreetID IS NULL))
          AND (House_Number = @NormHouse OR (House_Number IS NULL AND @NormHouse IS NULL));

        IF @@ROWCOUNT > 0
        BEGIN
            SELECT @Location_ID = Location_ID FROM location WHERE TlvAddressId = @TlvAddressId;
            RETURN;
        END

        BEGIN TRY
            INSERT INTO location (City_ID, Street_ID, House_Number, Latitude, Longitude, TlvAddressId, Gush, Chelka)
            VALUES (@CityID, @StreetID, @NormHouse, @Lat, @Lng, @TlvAddressId, @Gush, @Chelka);
            SET @Location_ID = SCOPE_IDENTITY();
        END TRY
        BEGIN CATCH
            -- A concurrent resolve won the race and inserted the same id_ktovet.
            IF ERROR_NUMBER() IN (2601, 2627)
                SELECT @Location_ID = Location_ID FROM location WHERE TlvAddressId = @TlvAddressId;
            ELSE
                THROW;
        END CATCH
        RETURN;
    END

    -- ===== 2. Coord fallback: fine geo grid cell (~1m), non-TLV / unaddressed =====
    IF @GeoKey IS NOT NULL
    BEGIN
        SELECT @Location_ID = Location_ID FROM location
        WHERE GeoKey = @GeoKey AND TlvAddressId IS NULL;

        IF @Location_ID IS NOT NULL
        BEGIN
            UPDATE location
            SET Street_ID    = COALESCE(Street_ID, @StreetID),
                House_Number = COALESCE(NULLIF(House_Number, N''), @NormHouse)
            WHERE Location_ID = @Location_ID;
            RETURN;
        END

        -- Adopt a pre-existing coord-less text-triple row for this address.
        UPDATE TOP (1) location
        SET Latitude = @Lat, Longitude = @Lng
        WHERE Latitude IS NULL AND Longitude IS NULL AND TlvAddressId IS NULL
          AND City_ID = @CityID
          AND (Street_ID = @StreetID OR (Street_ID IS NULL AND @StreetID IS NULL))
          AND (House_Number = @NormHouse OR (House_Number IS NULL AND @NormHouse IS NULL));

        IF @@ROWCOUNT > 0
        BEGIN
            SELECT @Location_ID = Location_ID FROM location WHERE GeoKey = @GeoKey AND TlvAddressId IS NULL;
            RETURN;
        END

        BEGIN TRY
            INSERT INTO location (City_ID, Street_ID, House_Number, Latitude, Longitude)
            VALUES (@CityID, @StreetID, @NormHouse, @Lat, @Lng);
            SET @Location_ID = SCOPE_IDENTITY();
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() IN (2601, 2627)
                SELECT @Location_ID = Location_ID FROM location WHERE GeoKey = @GeoKey AND TlvAddressId IS NULL;
            ELSE
                THROW;
        END CATCH
        RETURN;
    END

    -- ===== 3. No coords: text triple =====
    SELECT @Location_ID = Location_ID FROM location
    WHERE City_ID = @CityID
      AND (Street_ID = @StreetID OR (Street_ID IS NULL AND @StreetID IS NULL))
      AND (House_Number = @NormHouse OR (House_Number IS NULL AND @NormHouse IS NULL));

    IF @Location_ID IS NULL
    BEGIN
        INSERT INTO location (City_ID, Street_ID, House_Number)
        VALUES (@CityID, @StreetID, @NormHouse);
        SET @Location_ID = SCOPE_IDENTITY();
    END
END
GO

-- ---- Auth ----

CREATE OR ALTER PROCEDURE sp_RegisterUser
    @Email         NVARCHAR(255),
    @Password_Hash NVARCHAR(255),
    @Full_Name     NVARCHAR(255),
    @Phone         NVARCHAR(50)  = NULL,
    @City_Name     NVARCHAR(255) = NULL,
    @Street_Name   NVARCHAR(255) = NULL,
    @House_Number  NVARCHAR(50)  = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM [user] WHERE Email = @Email)
    BEGIN
        RAISERROR('EMAIL_EXISTS', 16, 1);
        RETURN;
    END

    DECLARE @LocationID INT = NULL;

    IF @City_Name IS NOT NULL AND LEN(TRIM(@City_Name)) > 0
    BEGIN
        -- Registration carries no coordinates, so this resolves via the text
        -- triple. Routing through the canonical resolver (rather than a raw
        -- INSERT) means the row gets unified with the geo-keyed canonical row
        -- the first time a coord-bearing resolve hits the same address.
        EXEC sp_ResolveLocationCanonical
            @City_Name = @City_Name, @Street_Name = @Street_Name,
            @House_Number = @House_Number, @Location_ID = @LocationID OUTPUT;
    END

    INSERT INTO [user] (Email, Password_Hash, Full_Name, Phone, User_Address)
    VALUES (@Email, @Password_Hash, @Full_Name, @Phone, @LocationID);

    DECLARE @UserID INT = SCOPE_IDENTITY();

    SELECT User_ID, Email, Full_Name, Phone, XP, User_Role, Onboarding_Done
    FROM [user] WHERE User_ID = @UserID;
END
GO

CREATE OR ALTER PROCEDURE sp_GetUserByEmail
    @Email NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT User_ID, Email, Password_Hash, Full_Name, Phone, User_Address, XP,
           Streak_Days, Last_Active, User_Role, User_Reason, Onboarding_Done, Created_At, Is_Active
    FROM [user] WHERE Email = @Email AND Is_Active = 1;
END
GO

CREATE OR ALTER PROCEDURE sp_GetUserById
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- ── Reporting streak (current + best-ever), derived from event history ──
    -- Gaps-and-islands over the distinct days the user filed a report.
    --   @StreakDays = current run, ending today or yesterday (UTC) → shown as
    --     the "ימי רצף" stat. (The persisted [user].Streak_Days column was never
    --     maintained by any write path, so it is ignored in favour of this.)
    --   @BestStreak = longest run ever → drives the permanent 7-day badge below.
    DECLARE @StreakDays INT = 0, @BestStreak INT = 0;
    ;WITH report_days AS (
        SELECT DISTINCT CAST(Created_At AS DATE) AS rd
        FROM event WHERE User_ID = @User_ID
    ),
    islands AS (
        SELECT rd, DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY rd), rd) AS grp
        FROM report_days
    ),
    runs AS (
        SELECT COUNT(*) AS len, MAX(rd) AS last_day
        FROM islands GROUP BY grp
    )
    SELECT
        @BestStreak = ISNULL(MAX(len), 0),
        @StreakDays = ISNULL(MAX(CASE WHEN last_day >= CAST(DATEADD(DAY, -1, SYSUTCDATETIME()) AS DATE)
                                      THEN len END), 0)
    FROM runs;

    -- ── Stats that drive achievement badges ──
    DECLARE @Events  INT = (SELECT COUNT(*) FROM event             WHERE User_ID = @User_ID);
    DECLARE @Favs    INT = (SELECT COUNT(*) FROM add_to_favorites  WHERE User_ID = @User_ID);
    DECLARE @Comps   INT = (SELECT COUNT(*) FROM comparison        WHERE User_ID = @User_ID);
    DECLARE @Reviews INT = (SELECT COUNT(*) FROM user_review       WHERE User_ID = @User_ID);
    DECLARE @Comments INT = (SELECT COUNT(*) FROM comment          WHERE User_ID = @User_ID);
    DECLARE @Upvotes INT = (SELECT ISNULL(SUM(v.Vote), 0) FROM event_vote v
                            INNER JOIN event e ON v.Event_ID = e.Event_ID
                            WHERE e.User_ID = @User_ID AND v.Vote = 1);
    DECLARE @XP      INT = (SELECT XP FROM [user] WHERE User_ID = @User_ID);

    -- ── Grant any newly-earned badges (permanent, idempotent) ──
    -- "Once earned, always earned": a row in user_badge is never removed, so a
    -- badge stays lit even if the stat later drops (streak resets, an upvote is
    -- withdrawn). The 7-day badge uses @BestStreak, so it is earned the moment a
    -- 7-day run ever completes and is never lost.
    -- THRESHOLDS MUST MATCH computeBadges() in
    --   03-client/src/app/components/personal/lib/levelSystem.ts
    -- Best-effort: wrapped so a concurrent-read PK race can never fail the
    -- profile read — the grant simply retries on the next load.
    BEGIN TRY
        INSERT INTO user_badge (User_ID, Badge_ID)
        SELECT @User_ID, v.id
        FROM (VALUES
            ('first',    CASE WHEN @Events     >= 1   THEN 1 ELSE 0 END),
            ('five',     CASE WHEN @Events     >= 5   THEN 1 ELSE 0 END),
            ('streak',   CASE WHEN @BestStreak >= 7   THEN 1 ELSE 0 END),
            ('upvotes',  CASE WHEN @Upvotes    >= 50  THEN 1 ELSE 0 END),
            ('reviewer', CASE WHEN @Reviews    >= 10  THEN 1 ELSE 0 END),
            ('champion', CASE WHEN @XP         >= 300 THEN 1 ELSE 0 END)
        ) v(id, met)
        WHERE v.met = 1
          AND NOT EXISTS (SELECT 1 FROM user_badge ub
                          WHERE ub.User_ID = @User_ID AND ub.Badge_ID = v.id);
    END TRY
    BEGIN CATCH
        -- Grant is best-effort and self-heals on the next read; never block the profile.
    END CATCH

    SELECT u.User_ID, u.Email, u.Full_Name, u.Phone, u.XP,
           @StreakDays AS Streak_Days,
           u.Last_Active, u.User_Role, u.User_Reason, u.Onboarding_Done, u.Created_At,
           u.User_Address,
           c.City_Name    AS Home_City_Name,
           s.Street_Name  AS Home_Street_Name,
           l.House_Number AS Home_House_Number,
           l.Latitude     AS Home_Lat,
           l.Longitude    AS Home_Lng,
           @Events  AS EventsCreated,
           @Favs    AS Favorites,
           @Comps   AS Comparisons,
           @Reviews AS ReviewsWritten,
           @Comments AS CommentsWritten,
           @Upvotes AS UpvotesReceived,
           (SELECT STRING_AGG(Badge_ID, ',') FROM user_badge WHERE User_ID = @User_ID) AS Earned_Badges
    FROM [user] u
    LEFT JOIN location l ON u.User_Address = l.Location_ID
    LEFT JOIN city c     ON l.City_ID = c.City_ID
    LEFT JOIN streets s  ON l.Street_ID = s.Street_ID
    WHERE u.User_ID = @User_ID AND u.Is_Active = 1;
END
GO

CREATE OR ALTER PROCEDURE sp_UpsertGoogleUser
    @Email     NVARCHAR(255),
    @Full_Name NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserID INT;
    SELECT @UserID = User_ID FROM [user] WHERE Email = @Email AND Is_Active = 1;

    IF @UserID IS NULL
    BEGIN
        INSERT INTO [user] (Email, Password_Hash, Full_Name) VALUES (@Email, '!', @Full_Name);
        SET @UserID = SCOPE_IDENTITY();
    END
    ELSE
    BEGIN
        UPDATE [user]
        SET Full_Name   = CASE WHEN LEN(ISNULL(Full_Name, '')) = 0 THEN @Full_Name ELSE Full_Name END,
            Last_Active = CAST(SYSUTCDATETIME() AS DATE)
        WHERE User_ID = @UserID;
    END

    SELECT User_ID, Email, Full_Name, Phone, XP, Streak_Days, User_Role,
           User_Reason, Onboarding_Done, Created_At
    FROM [user] WHERE User_ID = @UserID;
END
GO

CREATE OR ALTER PROCEDURE sp_DeleteUserAccount
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [user]
        SET Email         = CONCAT('deleted-user-', User_ID, '@deleted.local'),
            Password_Hash = '!',
            Full_Name     = CONCAT('deleted-user-', User_ID),
            Phone         = NULL,
            User_Address  = NULL,
            User_Role     = NULL,
            User_Reason   = NULL,
            Is_Active     = 0
        WHERE User_ID = @User_ID;

        UPDATE refresh_token
        SET Revoked_At = SYSUTCDATETIME()
        WHERE User_ID = @User_ID AND Revoked_At IS NULL;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ---- Refresh tokens (stored hashed — SHA-256 hex, done in C# before calling SP) ----

CREATE OR ALTER PROCEDURE sp_SaveRefreshToken
    @User_ID    INT,
    @Token      NVARCHAR(500),
    @Expires_At DATETIME2
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO refresh_token (User_ID, Token, Expires_At) VALUES (@User_ID, @Token, @Expires_At);
END
GO

-- Returns the token row whether or not it is revoked, plus its rotation
-- metadata (Replaced_By, Reused_At) and an Age_Seconds-since-revocation value.
-- The caller (AuthController.Refresh) decides what to do:
--   * active (Revoked_At IS NULL)                 → normal rotation
--   * revoked recently + has Replaced_By          → racing refresh: hand back successor
--   * revoked + no successor / outside grace win  → reuse signal: revoke the family
CREATE OR ALTER PROCEDURE sp_GetRefreshToken
    @Token NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        Token_ID,
        User_ID,
        Token,
        Expires_At,
        Created_At,
        Revoked_At,
        Replaced_By,
        Reused_At,
        DATEDIFF(SECOND, Revoked_At, SYSUTCDATETIME()) AS Revoked_Age_Seconds
    FROM refresh_token
    WHERE Token = @Token;
END
GO

CREATE OR ALTER PROCEDURE sp_RevokeRefreshToken
    @Token NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE refresh_token SET Revoked_At = SYSUTCDATETIME() WHERE Token = @Token;
END
GO

-- Atomic rotation: revoke the presented token, record the successor on it, and
-- insert the new token — all in one transaction so a crash can never leave the
-- old token revoked with no replacement (the unrecoverable-session bug).
-- Only rotates if the old token is still active; @Rotated reports whether it ran.
CREATE OR ALTER PROCEDURE sp_RotateRefreshToken
    @OldToken   NVARCHAR(500),
    @NewToken   NVARCHAR(500),
    @User_ID    INT,
    @Expires_At DATETIME2,
    @Rotated    BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @Rotated = 0;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Revoke + link, but only if currently active. Row count tells us
        -- whether we won the race (1) or another request already rotated it (0).
        UPDATE refresh_token
        SET Revoked_At  = SYSUTCDATETIME(),
            Replaced_By = @NewToken
        WHERE Token = @OldToken AND Revoked_At IS NULL;

        IF @@ROWCOUNT = 1
        BEGIN
            INSERT INTO refresh_token (User_ID, Token, Expires_At)
            VALUES (@User_ID, @NewToken, @Expires_At);
            SET @Rotated = 1;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- Reuse detection: a revoked token was replayed outside the grace window.
-- Stamp Reused_At on the replayed token, revoke every still-active token for
-- the user, AND null out Replaced_By across the whole family so NO family token
-- can satisfy the grace-window path afterwards (a token revoked-within-grace
-- must not be able to mint a fresh session after the family was compromised).
-- Result: a clean re-login is forced everywhere (defends against a stolen
-- refresh token).
CREATE OR ALTER PROCEDURE sp_RevokeRefreshTokenFamily
    @Token   NVARCHAR(500),
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE refresh_token
        SET Reused_At = SYSUTCDATETIME()
        WHERE Token = @Token AND Reused_At IS NULL;

        -- Revoke active tokens and strip the rotation link from every family
        -- token in one pass. Clearing Replaced_By is what closes the
        -- grace-window resurrection gap.
        UPDATE refresh_token
        SET Revoked_At  = CASE WHEN Revoked_At IS NULL THEN SYSUTCDATETIME() ELSE Revoked_At END,
            Replaced_By = NULL
        WHERE User_ID = @User_ID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ---- Password reset tokens (raw token never stored — C# hashes before insert) ----

CREATE OR ALTER PROCEDURE sp_CreatePasswordResetToken
    @User_ID    INT,
    @Token_Hash CHAR(64),
    @Expires_At DATETIME2,
    @Created_IP NVARCHAR(45) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO password_reset_token (User_ID, Token_Hash, Expires_At, Created_IP)
    VALUES (@User_ID, @Token_Hash, @Expires_At, @Created_IP);
END
GO

CREATE OR ALTER PROCEDURE sp_GetPasswordResetToken
    @Token_Hash CHAR(64)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Token_ID, User_ID, Token_Hash, Expires_At, Used_At, Created_At
    FROM password_reset_token
    WHERE Token_Hash = @Token_Hash;
END
GO

-- Marks the token used AND revokes all the user's refresh tokens in a single
-- transaction. The password-reset flow finishes by rotating the password, and
-- we want every existing session invalidated so a stolen access/refresh token
-- can no longer ride past the reset.
CREATE OR ALTER PROCEDURE sp_ConsumePasswordResetToken
    @Token_Hash CHAR(64)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @User_ID INT;

        SELECT @User_ID = User_ID
        FROM password_reset_token
        WHERE Token_Hash = @Token_Hash AND Used_At IS NULL;

        IF @User_ID IS NULL
        BEGIN
            ROLLBACK TRANSACTION;
            RETURN;
        END

        UPDATE password_reset_token
        SET Used_At = SYSUTCDATETIME()
        WHERE Token_Hash = @Token_Hash;

        UPDATE refresh_token
        SET Revoked_At = SYSUTCDATETIME()
        WHERE User_ID = @User_ID AND Revoked_At IS NULL;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_UpdateUserPassword
    @User_ID       INT,
    @Password_Hash NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [user]
    SET Password_Hash = @Password_Hash
    WHERE User_ID = @User_ID AND Is_Active = 1;
END
GO

-- ---- Events ----

CREATE OR ALTER PROCEDURE sp_CreateEvent
    @City_Name    NVARCHAR(255),
    @Street_Name  NVARCHAR(255) = NULL,
    @House_Number NVARCHAR(50)  = NULL,
    @EventType_ID INT,
    @User_ID      INT,
    @Start_Date   DATETIME,
    @End_Date     DATETIME      = NULL,
    @Description  NVARCHAR(MAX) = NULL,
    @EventStatus  NVARCHAR(100),
    @Picture      NVARCHAR(MAX) = NULL,
    @Lat          FLOAT         = NULL,
    @Lng          FLOAT         = NULL,
    @TlvAddressId INT           = NULL,
    @Gush         INT           = NULL,
    @Chelka       INT           = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Resolve to the canonical row for this address (id_ktovet identity when
        -- the caller resolved one, fine geo cell / text otherwise) so reports for
        -- the same address always land on one Location_ID. @NormCity/@NormStreet
        -- are still needed below for the activity-feed message.
        DECLARE @NormHouse  NVARCHAR(50)  = NULLIF(LTRIM(RTRIM(@House_Number)), '');
        DECLARE @CityID INT, @StreetID INT;
        DECLARE @NormCity NVARCHAR(255), @NormStreet NVARCHAR(255);
        EXEC sp_ResolveCityStreet @City_Name, @Street_Name,
             @CityID OUTPUT, @StreetID OUTPUT, @NormCity OUTPUT, @NormStreet OUTPUT;

        DECLARE @LocationID INT;
        EXEC sp_ResolveLocationCanonical
            @City_Name = @City_Name, @Street_Name = @Street_Name, @House_Number = @House_Number,
            @Lat = @Lat, @Lng = @Lng, @TlvAddressId = @TlvAddressId, @Gush = @Gush, @Chelka = @Chelka,
            @Location_ID = @LocationID OUTPUT;

        INSERT INTO event (Location_ID, EventType_ID, User_ID, Start_Date, End_Date, Description, EventStatus, Picture)
        VALUES (@LocationID, @EventType_ID, @User_ID, @Start_Date, @End_Date, @Description, @EventStatus, @Picture);

        DECLARE @EventID INT = SCOPE_IDENTITY();

        UPDATE [user] SET XP = XP + 20 WHERE User_ID = @User_ID;
        DECLARE @ActivityDesc NVARCHAR(500) =
            CASE
                WHEN @NormStreet IS NOT NULL AND @NormHouse IS NOT NULL
                    THEN N'יצרת דיווח - ' + @NormStreet + N' ' + @NormHouse + N', ' + @NormCity
                WHEN @NormStreet IS NOT NULL
                    THEN N'יצרת דיווח - ' + @NormStreet + N', ' + @NormCity
                ELSE N'יצרת דיווח - ' + @NormCity
            END;
        INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned, Location_ID)
        VALUES (@User_ID, 'event_created', @ActivityDesc, 20, @LocationID);

        COMMIT TRANSACTION;
        SELECT @EventID AS Event_ID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_GetEventsByLocation
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT e.Event_ID, e.Description, e.EventStatus, e.Start_Date, e.End_Date, e.Picture, e.Created_At,
           et.Name AS EventType_Name,
           u.Full_Name AS Author,
           ISNULL((SELECT SUM(Vote) FROM event_vote WHERE Event_ID = e.Event_ID), 0) AS Vote_Count
    FROM event e
    INNER JOIN event_type et ON e.EventType_ID = et.EventType_ID
    INNER JOIN [user] u      ON e.User_ID = u.User_ID
    WHERE e.Location_ID = @Location_ID
      AND ISNULL(e.Is_Closed, 0) = 0
    ORDER BY e.Created_At DESC;
END
GO

CREATE OR ALTER PROCEDURE sp_GetNearbyEvents
    @Lat    FLOAT,
    @Lng    FLOAT,
    @Radius INT = 200
AS
BEGIN
    SET NOCOUNT ON;

    SELECT e.Event_ID, e.Description, e.EventStatus, e.Start_Date, e.End_Date, e.Picture, e.Created_At,
           et.Name AS EventType_Name,
           u.Full_Name AS Author,
           c.City_Name, s.Street_Name, l.House_Number,
           l.Latitude AS Lat, l.Longitude AS Lng,
           ISNULL((SELECT SUM(Vote) FROM event_vote WHERE Event_ID = e.Event_ID), 0) AS Vote_Count,
           6371000 * ACOS(
               COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
               COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
               SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))
           ) AS Distance_M
    FROM event e
    INNER JOIN location l    ON e.Location_ID = l.Location_ID
    INNER JOIN event_type et ON e.EventType_ID = et.EventType_ID
    INNER JOIN [user] u      ON e.User_ID = u.User_ID
    INNER JOIN city c        ON l.City_ID = c.City_ID
    LEFT  JOIN streets s     ON l.Street_ID = s.Street_ID
    WHERE ISNULL(e.Is_Closed, 0) = 0
      AND l.Latitude  IS NOT NULL
      AND l.Longitude IS NOT NULL
      -- Bounding-box pre-filter makes IX_location_geo sargable (seek not scan).
      -- 111000 m/degree lat; 94000 m/degree lng at ~32° N (Tel Aviv).
      AND l.Latitude  BETWEEN @Lat - (@Radius / 111000.0) AND @Lat + (@Radius / 111000.0)
      AND l.Longitude BETWEEN @Lng - (@Radius / 94000.0)  AND @Lng + (@Radius / 94000.0)
      -- Exact Haversine filter after the cheap bounding-box pass.
      AND 6371000 * ACOS(
              COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
              COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
              SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))
          ) <= @Radius
    ORDER BY Distance_M;
END
GO

-- ---- Votes ----

CREATE OR ALTER PROCEDURE sp_VoteOnEvent
    @User_ID  INT,
    @Event_ID INT,
    @Vote     SMALLINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        IF EXISTS (SELECT 1 FROM event_vote WHERE User_ID = @User_ID AND Event_ID = @Event_ID)
        BEGIN
            DECLARE @OldVote SMALLINT;
            SELECT @OldVote = Vote FROM event_vote WHERE User_ID = @User_ID AND Event_ID = @Event_ID;

            IF @OldVote = @Vote
                DELETE FROM event_vote WHERE User_ID = @User_ID AND Event_ID = @Event_ID;
            ELSE
                UPDATE event_vote SET Vote = @Vote, Voted_At = SYSUTCDATETIME()
                WHERE User_ID = @User_ID AND Event_ID = @Event_ID;
        END
        ELSE
        BEGIN
            INSERT INTO event_vote (User_ID, Event_ID, Vote) VALUES (@User_ID, @Event_ID, @Vote);

            IF @Vote = 1
            BEGIN
                DECLARE @CreatorID INT;
                SELECT @CreatorID = User_ID FROM event WHERE Event_ID = @Event_ID;
                IF @CreatorID IS NOT NULL
                BEGIN
                    UPDATE [user] SET XP = XP + 5 WHERE User_ID = @CreatorID;
                    INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned)
                    VALUES (@CreatorID, 'vote_received', N'קיבלת הצבעת רלוונטי', 5);
                END
            END
        END

        COMMIT TRANSACTION;
        SELECT ISNULL(SUM(Vote), 0) AS Vote_Count
        FROM event_vote WHERE Event_ID = @Event_ID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ---- Comments ----

CREATE OR ALTER PROCEDURE sp_AddComment
    @User_ID  INT,
    @Event_ID INT,
    @Text     NVARCHAR(MAX),
    @Picture  NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO comment (User_ID, Event_ID, [Text], Picture) VALUES (@User_ID, @Event_ID, @Text, @Picture);
        DECLARE @CommentID INT = SCOPE_IDENTITY();

        UPDATE [user] SET XP = XP + 5 WHERE User_ID = @User_ID;
        DECLARE @CommentAddr NVARCHAR(500);
        DECLARE @CommentLocID INT;
        SELECT @CommentAddr =
            CASE
                WHEN s.Street_Name IS NOT NULL AND l.House_Number IS NOT NULL
                    THEN s.Street_Name + N' ' + l.House_Number + N', ' + c.City_Name
                WHEN s.Street_Name IS NOT NULL
                    THEN s.Street_Name + N', ' + c.City_Name
                ELSE c.City_Name
            END,
            @CommentLocID = l.Location_ID
        FROM event e
        INNER JOIN location l ON e.Location_ID = l.Location_ID
        INNER JOIN city c     ON l.City_ID = c.City_ID
        LEFT  JOIN streets s  ON l.Street_ID = s.Street_ID
        WHERE e.Event_ID = @Event_ID;
        INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned, Location_ID)
        VALUES (@User_ID, 'comment', ISNULL(N'הוספת תגובה - ' + @CommentAddr, N'הוספת תגובה לדיווח'), 5, @CommentLocID);

        -- Notify the report (event) owner in the in-app feed. Skip self-comments
        -- so a reporter commenting on their own report isn't notified.
        DECLARE @EventOwnerID INT = (SELECT User_ID FROM event WHERE Event_ID = @Event_ID);
        IF @EventOwnerID IS NOT NULL AND @EventOwnerID <> @User_ID
        BEGIN
            INSERT INTO user_notification (Recipient_User_ID, Type, Event_ID, Actor_User_ID, Comment_ID)
            VALUES (@EventOwnerID, N'comment', @Event_ID, @User_ID, @CommentID);
        END

        COMMIT TRANSACTION;
        SELECT @CommentID AS Comment_ID, @User_ID AS User_ID,
               (SELECT Full_Name FROM [user] WHERE User_ID = @User_ID) AS Author,
               @Text AS [Text], SYSUTCDATETIME() AS Created_At;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_GetCommentsByEvent
    @Event_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.Comment_ID, c.User_ID, u.Full_Name AS Author, c.[Text], c.Picture, c.Created_At
    FROM comment c
    INNER JOIN [user] u ON c.User_ID = u.User_ID
    WHERE c.Event_ID = @Event_ID
    ORDER BY c.Created_At ASC;
END
GO

-- ---- In-app notification feed ----

-- Active (non-dismissed) notifications for a user, newest first. Joins through
-- to the event/location so the client can render an address and deep-link to
-- the report (via Lat/Lng), plus the actor name and comment text for context.
CREATE OR ALTER PROCEDURE sp_GetUserNotifications
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        n.Notification_ID,
        n.Type,
        n.Event_ID,
        n.Created_At,
        CAST(CASE WHEN n.Seen_At IS NULL THEN 0 ELSE 1 END AS BIT) AS Is_Seen,
        n.Actor_User_ID,
        actor.Full_Name AS Actor_Name,
        cm.[Text]       AS Comment_Text,
        et.Name         AS EventType_Name,
        l.Latitude      AS Lat,
        l.Longitude     AS Lng,
        c.City_Name,
        s.Street_Name,
        l.House_Number,
        CASE
            WHEN s.Street_Name IS NOT NULL AND l.House_Number IS NOT NULL
                THEN s.Street_Name + N' ' + l.House_Number + N', ' + c.City_Name
            WHEN s.Street_Name IS NOT NULL
                THEN s.Street_Name + N', ' + c.City_Name
            ELSE c.City_Name
        END AS Address
    FROM user_notification n
    LEFT JOIN [user]     actor ON actor.User_ID = n.Actor_User_ID
    LEFT JOIN comment    cm    ON cm.Comment_ID = n.Comment_ID
    LEFT JOIN event      e     ON e.Event_ID = n.Event_ID
    LEFT JOIN location   l     ON l.Location_ID = e.Location_ID
    LEFT JOIN city       c     ON c.City_ID = l.City_ID
    LEFT JOIN streets    s     ON s.Street_ID = l.Street_ID
    LEFT JOIN event_type et    ON et.EventType_ID = e.EventType_ID
    WHERE n.Recipient_User_ID = @User_ID
      AND n.Dismissed_At IS NULL
    ORDER BY n.Created_At DESC;
END
GO

-- Mark a single notification as seen (idempotent; scoped to the recipient so a
-- user can't touch someone else's rows).
CREATE OR ALTER PROCEDURE sp_MarkNotificationSeen
    @Notification_ID INT,
    @User_ID         INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE user_notification
    SET Seen_At = SYSUTCDATETIME()
    WHERE Notification_ID = @Notification_ID
      AND Recipient_User_ID = @User_ID
      AND Seen_At IS NULL;
END
GO

-- Dismiss (soft-remove) a single notification. Scoped to the recipient.
CREATE OR ALTER PROCEDURE sp_DismissNotification
    @Notification_ID INT,
    @User_ID         INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE user_notification
    SET Dismissed_At = SYSUTCDATETIME()
    WHERE Notification_ID = @Notification_ID
      AND Recipient_User_ID = @User_ID
      AND Dismissed_At IS NULL;
END
GO

-- Bulk-insert in-app feed rows for an event-scoped notification (e.g. a new
-- nearby event). @Recipient_User_IDs is a comma-separated list of user IDs
-- (the deduped push-fan-out audience). Skips recipients who already have an
-- active (non-dismissed) row for the same (event, type) so a re-fire — or a
-- user subscribed via both home-address and GPS — never stacks duplicate cards.
CREATE OR ALTER PROCEDURE sp_InsertEventNotifications
    @Event_ID           INT,
    @Type               NVARCHAR(40),
    @Actor_User_ID      INT = NULL,
    @Recipient_User_IDs NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO user_notification (Recipient_User_ID, Type, Event_ID, Actor_User_ID)
    SELECT DISTINCT TRY_CONVERT(INT, value), @Type, @Event_ID, @Actor_User_ID
    FROM STRING_SPLIT(@Recipient_User_IDs, ',') ids
    WHERE TRY_CONVERT(INT, value) IS NOT NULL
      AND NOT EXISTS (
          SELECT 1 FROM user_notification un
          WHERE un.Recipient_User_ID = TRY_CONVERT(INT, ids.value)
            AND un.Event_ID = @Event_ID
            AND un.Type = @Type
            AND un.Dismissed_At IS NULL
      );
END
GO

-- ---- Reviews ----

CREATE OR ALTER PROCEDURE sp_AddReview
    @Location_ID INT,
    @User_ID     INT,
    @Text        NVARCHAR(MAX) = NULL,
    @Rating      INT,
    @Is_Positive BIT,
    @Picture     NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO user_review (Location_ID, User_ID, [Text], Rating, Is_Positive, Picture)
        VALUES (@Location_ID, @User_ID, @Text, @Rating, @Is_Positive, @Picture);

        UPDATE [user] SET XP = XP + 10 WHERE User_ID = @User_ID;
        DECLARE @ReviewAddr NVARCHAR(500);
        SELECT @ReviewAddr =
            CASE
                WHEN s.Street_Name IS NOT NULL AND l.House_Number IS NOT NULL
                    THEN s.Street_Name + N' ' + l.House_Number + N', ' + c.City_Name
                WHEN s.Street_Name IS NOT NULL
                    THEN s.Street_Name + N', ' + c.City_Name
                ELSE c.City_Name
            END
        FROM location l
        INNER JOIN city c    ON l.City_ID = c.City_ID
        LEFT  JOIN streets s ON l.Street_ID = s.Street_ID
        WHERE l.Location_ID = @Location_ID;
        INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned, Location_ID)
        VALUES (@User_ID, 'review_written', ISNULL(N'כתבת חוות דעת - ' + @ReviewAddr, N'כתבת חוות דעת'), 10, @Location_ID);

        COMMIT TRANSACTION;
        SELECT SCOPE_IDENTITY() AS Review_ID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_GetReviewsByLocation
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT r.Review_ID, r.User_ID, u.Full_Name AS Author, r.[Text], r.Rating,
           r.Is_Positive, r.Picture, r.Created_At
    FROM user_review r
    INNER JOIN [user] u ON r.User_ID = u.User_ID
    WHERE r.Location_ID = @Location_ID
    ORDER BY r.Created_At DESC;
END
GO

CREATE OR ALTER PROCEDURE sp_UpdateReview
    @Review_ID   INT,
    @User_ID     INT,
    @Text        NVARCHAR(MAX),
    @Rating      INT,
    @Is_Positive BIT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE user_review
    SET [Text] = @Text, Rating = @Rating, Is_Positive = @Is_Positive
    WHERE Review_ID = @Review_ID AND User_ID = @User_ID;

    IF @@ROWCOUNT = 0
        THROW 50001, N'חוות הדעת לא נמצאה או שאין לך הרשאה לערוך אותה', 1;
END
GO

-- ---- Favorites ----

CREATE OR ALTER PROCEDURE sp_GetFavorites
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT f.Location_ID, f.Added_Date,
           c.City_Name, s.Street_Name, l.House_Number
    FROM add_to_favorites f
    INNER JOIN location l ON f.Location_ID = l.Location_ID
    INNER JOIN city c     ON l.City_ID = c.City_ID
    LEFT  JOIN streets s  ON l.Street_ID = s.Street_ID
    WHERE f.User_ID = @User_ID
    ORDER BY f.Added_Date DESC;
END
GO

CREATE OR ALTER PROCEDURE sp_ToggleFavorite
    @User_ID     INT,
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    -- SERIALIZABLE + transaction closes the check-then-write race window:
    -- two concurrent toggles can no longer both insert (or both delete).
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    DECLARE @Is_Favorited BIT;
    BEGIN TRY
        BEGIN TRANSACTION;

        IF EXISTS (SELECT 1 FROM add_to_favorites WITH (UPDLOCK, HOLDLOCK)
                   WHERE User_ID = @User_ID AND Location_ID = @Location_ID)
        BEGIN
            DELETE FROM add_to_favorites WHERE User_ID = @User_ID AND Location_ID = @Location_ID;
            SET @Is_Favorited = 0;
        END
        ELSE
        BEGIN
            INSERT INTO add_to_favorites (User_ID, Location_ID) VALUES (@User_ID, @Location_ID);
            SET @Is_Favorited = 1;
        END

        COMMIT TRANSACTION;
        SELECT @Is_Favorited AS Is_Favorited;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ---- Comparisons ----

CREATE OR ALTER PROCEDURE sp_SaveComparison
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO comparison (User_ID) VALUES (@User_ID);
        DECLARE @CompID INT = SCOPE_IDENTITY();

        UPDATE [user] SET XP = XP + 5 WHERE User_ID = @User_ID;
        INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned)
        VALUES (@User_ID, 'comparison', N'ביצעת השוואת כתובות', 5);

        COMMIT TRANSACTION;
        SELECT @CompID AS Comparison_ID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_AddComparisonAddress
    @Comparison_ID INT,
    @Location_ID   INT,
    @Score         INT           = NULL,
    @Is_Winner     BIT           = 0,
    @Address       NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO comparison_address (Comparison_ID, Location_ID, Score, Is_Winner, Address)
    VALUES (@Comparison_ID, @Location_ID, @Score, @Is_Winner, @Address);
END
GO

CREATE OR ALTER PROCEDURE sp_GetComparisonHistory
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.Comparison_ID, c.Created_At,
           ca.Location_ID, ca.Score, ca.Is_Winner, ca.Address,
           ci.City_Name, s.Street_Name, l.House_Number
    FROM comparison c
    INNER JOIN comparison_address ca ON c.Comparison_ID = ca.Comparison_ID
    INNER JOIN location l            ON ca.Location_ID = l.Location_ID
    INNER JOIN city ci               ON l.City_ID = ci.City_ID
    LEFT  JOIN streets s             ON l.Street_ID = s.Street_ID
    WHERE c.User_ID = @User_ID
    ORDER BY c.Created_At DESC, ca.Is_Winner DESC;
END
GO

-- ---- Location ----

-- Thin wrapper over the canonical resolver (returns the ID as a result set for
-- callers that ExecuteScalar). Pass @Lat/@Lng whenever the caller has them so
-- the row resolves by geo cell and can't fragment by spelling; without coords
-- it falls back to the text triple.
CREATE OR ALTER PROCEDURE sp_ResolveLocation
    @City_Name    NVARCHAR(255),
    @Street_Name  NVARCHAR(255) = NULL,
    @House_Number NVARCHAR(50)  = NULL,
    @Lat          FLOAT         = NULL,
    @Lng          FLOAT         = NULL,
    @TlvAddressId INT           = NULL,
    @Gush         INT           = NULL,
    @Chelka       INT           = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @LocationID INT;
    EXEC sp_ResolveLocationCanonical
        @City_Name = @City_Name, @Street_Name = @Street_Name, @House_Number = @House_Number,
        @Lat = @Lat, @Lng = @Lng, @TlvAddressId = @TlvAddressId, @Gush = @Gush, @Chelka = @Chelka,
        @Location_ID = @LocationID OUTPUT;
    SELECT @LocationID AS Location_ID;
END
GO

CREATE OR ALTER PROCEDURE sp_GetLocationById
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.Location_ID, l.Latitude, l.Longitude, l.House_Number,
           c.City_Name, s.Street_Name
    FROM location l
    INNER JOIN city c    ON l.City_ID = c.City_ID
    LEFT  JOIN streets s ON l.Street_ID = s.Street_ID
    WHERE l.Location_ID = @Location_ID;
END
GO

CREATE OR ALTER PROCEDURE sp_GetLocationStats
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        (SELECT COUNT(*) FROM event       WHERE Location_ID = @Location_ID) AS EventCount,
        (SELECT COUNT(*) FROM PlanStatus  WHERE Location_ID = @Location_ID) AS PlanningCount,
        (SELECT COUNT(*) FROM user_review WHERE Location_ID = @Location_ID) AS ReviewCount,
        (SELECT COUNT(*) FROM user_review WHERE Location_ID = @Location_ID AND Is_Positive = 1) AS PositiveReviews,
        (SELECT COUNT(*) FROM user_review WHERE Location_ID = @Location_ID AND Is_Positive = 0) AS NegativeReviews;
END
GO

-- ---- User home address ----

CREATE OR ALTER PROCEDURE sp_UpdateUserHomeAddress
    @User_ID      INT,
    @City_Name    NVARCHAR(255),
    @Street_Name  NVARCHAR(255) = NULL,
    @House_Number NVARCHAR(50)  = NULL,
    @Lat          FLOAT         = NULL,
    @Lng          FLOAT         = NULL,
    @TlvAddressId INT           = NULL,
    @Gush         INT           = NULL,
    @Chelka       INT           = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @City_Name IS NULL OR LEN(TRIM(@City_Name)) = 0
    BEGIN
        RAISERROR('City_Name is required', 16, 1);
        RETURN;
    END

    -- Canonical resolve (id_ktovet identity when the caller resolved one, fine
    -- geo cell / text otherwise) so a user's home shares the same Location_ID as
    -- everyone else's reports/reviews at that address.
    DECLARE @LocationID INT;
    EXEC sp_ResolveLocationCanonical
        @City_Name = @City_Name, @Street_Name = @Street_Name, @House_Number = @House_Number,
        @Lat = @Lat, @Lng = @Lng, @TlvAddressId = @TlvAddressId, @Gush = @Gush, @Chelka = @Chelka,
        @Location_ID = @LocationID OUTPUT;

    UPDATE [user] SET User_Address = @LocationID WHERE User_ID = @User_ID;
    SELECT @LocationID AS Location_ID;
END
GO

CREATE OR ALTER PROCEDURE sp_UpdateUserPhone
    @User_ID INT,
    @Phone   NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [user] SET Phone = @Phone WHERE User_ID = @User_ID;
END
GO

-- ---- Onboarding ----

CREATE OR ALTER PROCEDURE sp_SaveOnboarding
    @User_ID               INT,
    @User_Role             NVARCHAR(100),
    @User_Reason           NVARCHAR(255),
    @Is_Renter             BIT = 0,
    @Is_Owner              BIT = 0,
    @Is_LivingWithParents  BIT = 0,
    @Is_Investor           BIT = 0,
    @Is_StatusOther        BIT = 0,
    @Is_LookingForApartment BIT = 0,
    @Is_Resident           BIT = 0,
    @Is_RealEstateInvestor BIT = 0,
    @Is_Professional       BIT = 0,
    @Is_GovRepresentative  BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [user]
        SET User_Role = @User_Role, User_Reason = @User_Reason, Onboarding_Done = 1
        WHERE User_ID = @User_ID;

        IF EXISTS (SELECT 1 FROM survey WHERE User_ID = @User_ID)
            UPDATE survey SET
                Is_Renter = @Is_Renter, Is_Owner = @Is_Owner,
                Is_LivingWithParents = @Is_LivingWithParents,
                Is_Investor = @Is_Investor, Is_StatusOther = @Is_StatusOther,
                Is_LookingForApartment = @Is_LookingForApartment,
                Is_Resident = @Is_Resident, Is_RealEstateInvestor = @Is_RealEstateInvestor,
                Is_Professional = @Is_Professional, Is_GovRepresentative = @Is_GovRepresentative
            WHERE User_ID = @User_ID;
        ELSE
            INSERT INTO survey (User_ID, Is_Renter, Is_Owner, Is_LivingWithParents, Is_Investor,
                Is_StatusOther, Is_LookingForApartment, Is_Resident, Is_RealEstateInvestor,
                Is_Professional, Is_GovRepresentative)
            VALUES (@User_ID, @Is_Renter, @Is_Owner, @Is_LivingWithParents, @Is_Investor,
                @Is_StatusOther, @Is_LookingForApartment, @Is_Resident, @Is_RealEstateInvestor,
                @Is_Professional, @Is_GovRepresentative);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_SkipOnboarding
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [user] SET Onboarding_Done = 1 WHERE User_ID = @User_ID;
END
GO

-- ---- Activity log ----

CREATE OR ALTER PROCEDURE sp_GetUserActivity
    @User_ID INT,
    @Limit   INT = 20
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@Limit) Activity_ID, Action_Type, Description, XP_Earned, Location_ID, Created_At
    FROM user_activity
    WHERE User_ID = @User_ID
    ORDER BY Created_At DESC;
END
GO

-- sp_ClaimWeeklyMission
-- Claims a weekly mission XP reward for a user.
-- Returns 1 row: { Claimed BIT, XP_Awarded INT, Message NVARCHAR }.
-- Idempotent: a second call for the same (user, mission, week) returns
-- Claimed=0 with no side effects.

CREATE OR ALTER PROCEDURE sp_ClaimWeeklyMission
    @User_ID    INT,
    @Mission_ID NVARCHAR(40),
    @XP_Amount  INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @WeekKey INT =
        YEAR(GETUTCDATE()) * 100 + DATEPART(ISO_WEEK, GETUTCDATE());

    -- Guard: already claimed this week?
    IF EXISTS (
        SELECT 1 FROM user_mission_grant
        WHERE User_ID = @User_ID AND Mission_ID = @Mission_ID AND Week_Key = @WeekKey
    )
    BEGIN
        SELECT CAST(0 AS BIT) AS Claimed, 0 AS XP_Awarded, N'כבר תבעת משימה זו השבוע' AS [Message];
        RETURN;
    END

    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO user_mission_grant (User_ID, Mission_ID, Week_Key, XP_Awarded)
        VALUES (@User_ID, @Mission_ID, @WeekKey, @XP_Amount);

        UPDATE [user] SET XP = XP + @XP_Amount WHERE User_ID = @User_ID;

        INSERT INTO user_activity (User_ID, Action_Type, Description, XP_Earned)
        VALUES (@User_ID, 'mission_complete',
                N'השלמת משימה שבועית: ' + @Mission_ID, @XP_Amount);

        COMMIT TRANSACTION;
        SELECT CAST(1 AS BIT) AS Claimed, @XP_Amount AS XP_Awarded, N'כל הכבוד!' AS [Message];
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ---- Notification subscriptions ----

CREATE OR ALTER PROCEDURE sp_ToggleNotification
    @User_ID     INT,
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    -- SERIALIZABLE + transaction closes the check-then-write race window:
    -- two concurrent toggles can no longer both insert (or both delete).
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    DECLARE @Is_Subscribed BIT;
    BEGIN TRY
        BEGIN TRANSACTION;

        IF EXISTS (SELECT 1 FROM notification_subscription WITH (UPDLOCK, HOLDLOCK)
                   WHERE User_ID = @User_ID AND Location_ID = @Location_ID)
        BEGIN
            DELETE FROM notification_subscription WHERE User_ID = @User_ID AND Location_ID = @Location_ID;
            SET @Is_Subscribed = 0;
        END
        ELSE
        BEGIN
            INSERT INTO notification_subscription (User_ID, Location_ID) VALUES (@User_ID, @Location_ID);
            SET @Is_Subscribed = 1;
        END

        COMMIT TRANSACTION;
        SELECT @Is_Subscribed AS Is_Subscribed;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE sp_GetUserSubscriptions
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ns.Location_ID,
           ns.Created_At AS Subscribed_At,
           c.City_Name, s.Street_Name, l.House_Number,
           l.Latitude, l.Longitude,
           (SELECT COUNT(1) FROM event e
            WHERE e.Location_ID = ns.Location_ID
              AND e.EventStatus = N'קורה'
              AND ISNULL(e.Is_Closed, 0) = 0
              AND (e.End_Date IS NULL OR e.End_Date >= SYSUTCDATETIME())
              AND e.Start_Date <= SYSUTCDATETIME()) AS Active_Event_Count
    FROM notification_subscription ns
    INNER JOIN location l ON ns.Location_ID = l.Location_ID
    INNER JOIN city c     ON l.City_ID = c.City_ID
    LEFT  JOIN streets s  ON l.Street_ID = s.Street_ID
    WHERE ns.User_ID = @User_ID
    ORDER BY ns.Created_At DESC;
END
GO

-- ---- Device tokens (FCM push) ----

CREATE OR ALTER PROCEDURE sp_UpsertDeviceToken
    @User_ID  INT,
    @Token    NVARCHAR(500),
    @Platform NVARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
    MERGE device_token AS t
    USING (SELECT @Token AS Token) AS s ON t.Token = s.Token
    WHEN MATCHED THEN UPDATE SET
        User_ID      = @User_ID,
        Platform     = @Platform,
        Last_Seen_At = SYSUTCDATETIME()
    WHEN NOT MATCHED THEN
        INSERT (User_ID, Token, Platform) VALUES (@User_ID, @Token, @Platform);
END
GO

-- Ownership-checked delete — called on user logout.
CREATE OR ALTER PROCEDURE sp_DeleteDeviceToken
    @User_ID INT,
    @Token   NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM device_token WHERE Token = @Token AND User_ID = @User_ID;
END
GO

-- System-level delete — called by FcmSender when FCM returns "unregistered".
CREATE OR ALTER PROCEDURE sp_DeleteDeviceTokenByToken
    @Token NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM device_token WHERE Token = @Token;
END
GO

-- Fetch every device token belonging to users subscribed near the given location.
-- Falls back to exact-match when the event location has no coordinates.
CREATE OR ALTER PROCEDURE sp_GetTokensForLocation
    @Location_ID  INT,
    @RadiusMeters INT = 100
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Lat FLOAT, @Lng FLOAT;
    SELECT @Lat = Latitude, @Lng = Longitude FROM location WHERE Location_ID = @Location_ID;

    IF @Lat IS NULL OR @Lng IS NULL
    BEGIN
        SELECT dt.Token, dt.Platform, dt.User_ID
        FROM device_token dt
        INNER JOIN notification_subscription ns ON ns.User_ID = dt.User_ID
        WHERE ns.Location_ID = @Location_ID;
        RETURN;
    END

    -- Haversine argument clamped to [-1, 1] to avoid ACOS domain errors on identical coords.
    SELECT DISTINCT dt.Token, dt.Platform, dt.User_ID
    FROM device_token dt
    INNER JOIN notification_subscription ns ON ns.User_ID = dt.User_ID
    INNER JOIN location l                   ON l.Location_ID = ns.Location_ID
    WHERE l.Latitude  IS NOT NULL
      AND l.Longitude IS NOT NULL
      AND 6371000 * ACOS(
              CASE
                  WHEN (COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
                        COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))) >  1 THEN  1
                  WHEN (COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
                        COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))) < -1 THEN -1
                  ELSE (COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
                        COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude)))
              END
          ) <= @RadiusMeters;
END
GO

-- Fetch the report (event) owner's device tokens so we can push when someone
-- comments on their report. Returns no rows when the commenter is the owner
-- (no self-notification) or when the owner has no registered devices. The
-- formatted address mirrors the CASE expression used by sp_AddComment.
CREATE OR ALTER PROCEDURE sp_GetCommentPushTargets
    @Event_ID          INT,
    @Commenter_User_ID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT dt.Token,
           dt.Platform,
           e.User_ID    AS Owner_User_ID,
           e.Location_ID AS Location_ID,
           l.Latitude   AS Lat,
           l.Longitude  AS Lng,
           CASE
               WHEN s.Street_Name IS NOT NULL AND l.House_Number IS NOT NULL
                   THEN s.Street_Name + N' ' + l.House_Number + N', ' + c.City_Name
               WHEN s.Street_Name IS NOT NULL
                   THEN s.Street_Name + N', ' + c.City_Name
               ELSE c.City_Name
           END AS Address
    FROM event e
    INNER JOIN device_token dt ON dt.User_ID = e.User_ID
    INNER JOIN location l      ON e.Location_ID = l.Location_ID
    INNER JOIN city c          ON l.City_ID = c.City_ID
    LEFT  JOIN streets s       ON l.Street_ID = s.Street_ID
    WHERE e.Event_ID = @Event_ID
      AND e.User_ID <> @Commenter_User_ID;
END
GO

-- ---- GPS proximity subscriptions ----

-- Upsert the user's last GPS sample. Called from NearbyReportsScreen when the
-- "דיווחים בקרבתי" toggle is enabled and we receive a fresh fix.
CREATE OR ALTER PROCEDURE sp_UpsertGpsSubscription
    @User_ID INT,
    @Lat     FLOAT,
    @Lng     FLOAT,
    @Enabled BIT
AS
BEGIN
    SET NOCOUNT ON;

    MERGE gps_notification_subscription AS target
    USING (SELECT @User_ID AS User_ID) AS src
        ON target.User_ID = src.User_ID
    WHEN MATCHED THEN
        UPDATE SET Last_Lat = @Lat, Last_Lng = @Lng,
                   Is_Enabled = @Enabled, Updated_At = SYSUTCDATETIME()
    WHEN NOT MATCHED THEN
        INSERT (User_ID, Last_Lat, Last_Lng, Is_Enabled)
        VALUES (@User_ID, @Lat, @Lng, @Enabled);
END
GO

CREATE OR ALTER PROCEDURE sp_DisableGpsSubscription
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE gps_notification_subscription
       SET Is_Enabled = 0, Updated_At = SYSUTCDATETIME()
     WHERE User_ID = @User_ID;
END
GO

CREATE OR ALTER PROCEDURE sp_GetGpsSubscription
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT User_ID, Last_Lat, Last_Lng, Is_Enabled, Updated_At
      FROM gps_notification_subscription
     WHERE User_ID = @User_ID;
END
GO

-- Fan-out: every device token whose owner has GPS proximity enabled, was seen
-- within the freshness window, and whose last known position is within
-- @RadiusMeters of (@Lat, @Lng). Excludes stale rows older than 30 days so we
-- don't push to users who haven't opened the app in a month.
CREATE OR ALTER PROCEDURE sp_GetGpsTokensNearby
    @Lat          FLOAT,
    @Lng          FLOAT,
    @RadiusMeters INT = 200
AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT dt.Token, dt.Platform, dt.User_ID
    FROM device_token dt
    INNER JOIN gps_notification_subscription g ON g.User_ID = dt.User_ID
    WHERE g.Is_Enabled = 1
      AND g.Updated_At >= DATEADD(day, -30, SYSUTCDATETIME())
      AND 6371000 * ACOS(
              CASE
                  WHEN (COS(RADIANS(@Lat)) * COS(RADIANS(g.Last_Lat)) *
                        COS(RADIANS(g.Last_Lng) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(g.Last_Lat))) >  1 THEN  1
                  WHEN (COS(RADIANS(@Lat)) * COS(RADIANS(g.Last_Lat)) *
                        COS(RADIANS(g.Last_Lng) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(g.Last_Lat))) < -1 THEN -1
                  ELSE (COS(RADIANS(@Lat)) * COS(RADIANS(g.Last_Lat)) *
                        COS(RADIANS(g.Last_Lng) - RADIANS(@Lng)) +
                        SIN(RADIANS(@Lat)) * SIN(RADIANS(g.Last_Lat)))
              END
          ) <= @RadiusMeters;
END
GO

-- ---- Plan status ----

CREATE OR ALTER PROCEDURE sp_GetPlansByLocation
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Plan_ID, Work_Type, Work_Scope, Request_Essence, Start_Date, End_Date, Current_Stage
    FROM PlanStatus WHERE Location_ID = @Location_ID ORDER BY Start_Date DESC;
END
GO

-- ---- City / street lookups ----

CREATE OR ALTER PROCEDURE sp_GetAllCities
AS
BEGIN
    SET NOCOUNT ON;
    SELECT City_ID, City_Name, District FROM city ORDER BY City_Name;
END
GO

CREATE OR ALTER PROCEDURE sp_GetStreetsByCity
    @City_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Street_ID, Street_Name FROM streets WHERE City_ID = @City_ID ORDER BY Street_Name;
END
GO

-- ---- ML features (smart comparison scoring) ----

CREATE OR ALTER PROCEDURE sp_GetLocationFeatures
    @Location_ID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Lat FLOAT, @Lng FLOAT;
    SELECT @Lat = Latitude, @Lng = Longitude FROM location WHERE Location_ID = @Location_ID;

    DECLARE @Radius INT = 50; -- 50 m radius for nearby disruptions

    SELECT
        (SELECT COUNT(*) FROM event WHERE Location_ID = @Location_ID)                          AS EventCount,
        -- "Active" = genuinely happening now (see sp_GetLocationsInBbox for the
        -- rationale). EventStatus alone is a static label that never expires.
        (SELECT COUNT(*) FROM event
          WHERE Location_ID = @Location_ID
            AND EventStatus = N'קורה'
            AND ISNULL(Is_Closed, 0) = 0
            AND (End_Date IS NULL OR End_Date >= SYSUTCDATETIME())
            AND Start_Date <= SYSUTCDATETIME())                                                AS ActiveEventCount,
        (SELECT COUNT(*) FROM event
          WHERE Location_ID = @Location_ID
            AND EventStatus = N'עתיד לקרות'
            AND ISNULL(Is_Closed, 0) = 0)                                                      AS UpcomingEventCount,
        ISNULL((SELECT AVG(CAST(sub.VoteSum AS FLOAT))
                FROM (SELECT ISNULL(SUM(v.Vote), 0) AS VoteSum
                      FROM event e LEFT JOIN event_vote v ON e.Event_ID = v.Event_ID
                      WHERE e.Location_ID = @Location_ID GROUP BY e.Event_ID) sub), 0) AS AvgEventVoteScore,
        (SELECT COUNT(*) FROM user_review WHERE Location_ID = @Location_ID)                    AS ReviewCount,
        ISNULL((SELECT AVG(CAST(Rating AS FLOAT)) FROM user_review WHERE Location_ID = @Location_ID), 0) AS AvgRating,
        ISNULL((SELECT AVG(CAST(Is_Positive AS FLOAT)) FROM user_review WHERE Location_ID = @Location_ID), 0.5) AS PositiveRatio,
        (SELECT COUNT(*) FROM PlanStatus WHERE Location_ID = @Location_ID)                     AS PlanningCount,
        (SELECT COUNT(*) FROM add_to_favorites WHERE Location_ID = @Location_ID)               AS FavoritesCount,
        ISNULL((SELECT COUNT(*) FROM comment c INNER JOIN event e ON c.Event_ID = e.Event_ID
                WHERE e.Location_ID = @Location_ID), 0)                                        AS CommentCount,
        CASE WHEN @Lat IS NOT NULL AND @Lng IS NOT NULL THEN
            (SELECT COUNT(*) FROM event e INNER JOIN location l ON e.Location_ID = l.Location_ID
             WHERE l.Latitude IS NOT NULL AND l.Longitude IS NOT NULL
               AND l.Location_ID <> @Location_ID
               AND 6371000 * ACOS(
                   COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
                   COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
                   SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))
               ) <= @Radius)
        ELSE 0 END AS NearbyEventCount,
        CASE WHEN @Lat IS NOT NULL AND @Lng IS NOT NULL THEN
            (SELECT COUNT(*) FROM event e INNER JOIN location l ON e.Location_ID = l.Location_ID
             WHERE l.Latitude IS NOT NULL AND l.Longitude IS NOT NULL
               AND l.Location_ID <> @Location_ID
               AND e.EventStatus = N'קורה'
               AND ISNULL(e.Is_Closed, 0) = 0
               AND (e.End_Date IS NULL OR e.End_Date >= SYSUTCDATETIME())
               AND e.Start_Date <= SYSUTCDATETIME()
               AND 6371000 * ACOS(
                   COS(RADIANS(@Lat)) * COS(RADIANS(l.Latitude)) *
                   COS(RADIANS(l.Longitude) - RADIANS(@Lng)) +
                   SIN(RADIANS(@Lat)) * SIN(RADIANS(l.Latitude))
               ) <= @Radius)
        ELSE 0 END AS NearbyActiveEventCount;
END
GO

-- ============================================================
-- NEIGHBORHOOD CLUSTERING (ML — K-Means, Module 5)
--
-- Two tables:
--   neighborhood          — static polygon geometry (seeded once from TA GIS layer 511)
--   neighborhood_cluster  — dynamic cluster assignments (rewritten weekly)
--
-- Lookups for the bbox-prefilter pattern: SQL filters by lat/lng range
-- (uses the existing IX_location_geo index) and C# refines with point-in-
-- polygon, since this DB does not use the SQL `geography` type.
-- ============================================================

IF OBJECT_ID('dbo.neighborhood', 'U') IS NULL
CREATE TABLE neighborhood (
    Neighborhood_ID    INT IDENTITY(1,1) PRIMARY KEY,
    Shem_Shchuna       NVARCHAR(200) NOT NULL UNIQUE,
    Polygon_GeoJSON    NVARCHAR(MAX) NOT NULL,
    Centroid_Lat       FLOAT NOT NULL,
    Centroid_Lng       FLOAT NOT NULL,
    Min_Lat            FLOAT NOT NULL,
    Max_Lat            FLOAT NOT NULL,
    Min_Lng            FLOAT NOT NULL,
    Max_Lng            FLOAT NOT NULL,
    Area_Km2           FLOAT NOT NULL CHECK (Area_Km2 > 0),
    Created_At         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.neighborhood_cluster', 'U') IS NULL
CREATE TABLE neighborhood_cluster (
    Neighborhood_ID    INT NOT NULL PRIMARY KEY REFERENCES neighborhood(Neighborhood_ID),
    Cluster_ID         INT NOT NULL,
    Cluster_Label      NVARCHAR(20) NOT NULL CHECK (Cluster_Label IN (N'good', N'medium', N'bad')),
    Score              FLOAT NOT NULL CHECK (Score >= 0 AND Score <= 100),
    Feature_Means      NVARCHAR(MAX) NULL,
    Updated_At         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_neighborhood_cluster_label')
    CREATE INDEX IX_neighborhood_cluster_label ON neighborhood_cluster(Cluster_Label);
GO

-- ---- Stored procedures ----

CREATE OR ALTER PROCEDURE sp_GetNeighborhoodClusters
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        n.Neighborhood_ID,
        n.Shem_Shchuna,
        n.Polygon_GeoJSON,
        n.Centroid_Lat,
        n.Centroid_Lng,
        n.Area_Km2,
        c.Cluster_ID,
        c.Cluster_Label,
        c.Score,
        c.Feature_Means,
        c.Updated_At
    FROM neighborhood n
    INNER JOIN neighborhood_cluster c ON c.Neighborhood_ID = n.Neighborhood_ID
    ORDER BY n.Shem_Shchuna;
END
GO

CREATE OR ALTER PROCEDURE sp_GetAllNeighborhoods
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        Neighborhood_ID, Shem_Shchuna, Polygon_GeoJSON,
        Centroid_Lat, Centroid_Lng,
        Min_Lat, Max_Lat, Min_Lng, Max_Lng,
        Area_Km2
    FROM neighborhood
    ORDER BY Shem_Shchuna;
END
GO

CREATE OR ALTER PROCEDURE sp_InsertNeighborhood
    @Shem_Shchuna    NVARCHAR(200),
    @Polygon_GeoJSON NVARCHAR(MAX),
    @Centroid_Lat    FLOAT,
    @Centroid_Lng    FLOAT,
    @Min_Lat         FLOAT,
    @Max_Lat         FLOAT,
    @Min_Lng         FLOAT,
    @Max_Lng         FLOAT,
    @Area_Km2        FLOAT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO neighborhood (
        Shem_Shchuna, Polygon_GeoJSON,
        Centroid_Lat, Centroid_Lng,
        Min_Lat, Max_Lat, Min_Lng, Max_Lng,
        Area_Km2
    )
    VALUES (
        @Shem_Shchuna, @Polygon_GeoJSON,
        @Centroid_Lat, @Centroid_Lng,
        @Min_Lat, @Max_Lat, @Min_Lng, @Max_Lng,
        @Area_Km2
    );
    SELECT CAST(SCOPE_IDENTITY() AS INT) AS Neighborhood_ID;
END
GO

-- Bbox-prefilter aggregation. SQL returns rows that *might* be inside the polygon;
-- C# refines using NeighborhoodPolygon.PointInPolygon with the GeoJSON ring.
-- We return the per-location data as a result set, not an aggregate, because the
-- final filter happens in C#.
CREATE OR ALTER PROCEDURE sp_GetLocationsInBbox
    @Min_Lat FLOAT,
    @Max_Lat FLOAT,
    @Min_Lng FLOAT,
    @Max_Lng FLOAT
AS
BEGIN
    SET NOCOUNT ON;

    -- Per-location aggregates of all features the cluster job needs.
    -- Bbox prefilter uses IX_location_geo.
    SELECT
        l.Location_ID,
        l.Latitude,
        l.Longitude,
        ISNULL((SELECT COUNT(*) FROM event WHERE Location_ID = l.Location_ID), 0)                          AS EventCount,
        -- "Active" = genuinely happening right now, NOT merely labelled 'קורה'.
        -- EventStatus is a static label the reporter picks once and nothing ever
        -- transitions it, so without these guards a year-old report counts as
        -- active forever and inflates the neighborhood disruption signal. Gate on:
        --   * not closed by the 3-day relevance flow,
        --   * End_Date still in the future (or open-ended),
        --   * Start_Date already reached (excludes future 'עתיד לקרות' rows).
        ISNULL((SELECT COUNT(*) FROM event
                WHERE Location_ID = l.Location_ID
                  AND EventStatus = N'קורה'
                  AND ISNULL(Is_Closed, 0) = 0
                  AND (End_Date IS NULL OR End_Date >= SYSUTCDATETIME())
                  AND Start_Date <= SYSUTCDATETIME()), 0) AS ActiveEventCount,
        ISNULL((SELECT AVG(CAST(sub.VoteSum AS FLOAT))
                FROM (SELECT ISNULL(SUM(v.Vote), 0) AS VoteSum
                      FROM event e LEFT JOIN event_vote v ON e.Event_ID = v.Event_ID
                      WHERE e.Location_ID = l.Location_ID GROUP BY e.Event_ID) sub), 0) AS AvgEventVoteScore,
        ISNULL((SELECT COUNT(*) FROM user_review WHERE Location_ID = l.Location_ID), 0)                    AS ReviewCount,
        ISNULL((SELECT AVG(CAST(Rating AS FLOAT)) FROM user_review WHERE Location_ID = l.Location_ID), 0)  AS AvgRating,
        ISNULL((SELECT AVG(CAST(Is_Positive AS FLOAT)) FROM user_review WHERE Location_ID = l.Location_ID), 0.5) AS PositiveRatio,
        ISNULL((SELECT COUNT(*) FROM add_to_favorites WHERE Location_ID = l.Location_ID), 0)               AS FavoritesCount
    FROM location l
    WHERE l.Latitude IS NOT NULL AND l.Longitude IS NOT NULL
      AND l.Latitude  BETWEEN @Min_Lat AND @Max_Lat
      AND l.Longitude BETWEEN @Min_Lng AND @Max_Lng;
END
GO

CREATE OR ALTER PROCEDURE sp_UpsertNeighborhoodCluster
    @Neighborhood_ID INT,
    @Cluster_ID      INT,
    @Cluster_Label   NVARCHAR(20),
    @Score           FLOAT,
    @Feature_Means   NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE neighborhood_cluster
       SET Cluster_ID    = @Cluster_ID,
           Cluster_Label = @Cluster_Label,
           Score         = @Score,
           Feature_Means = @Feature_Means,
           Updated_At    = SYSUTCDATETIME()
     WHERE Neighborhood_ID = @Neighborhood_ID;

    IF @@ROWCOUNT = 0
        INSERT INTO neighborhood_cluster (Neighborhood_ID, Cluster_ID, Cluster_Label, Score, Feature_Means)
        VALUES (@Neighborhood_ID, @Cluster_ID, @Cluster_Label, @Score, @Feature_Means);
END
GO

CREATE OR ALTER PROCEDURE sp_GetClusterMaxUpdatedAt
AS
BEGIN
    SET NOCOUNT ON;
    SELECT MAX(Updated_At) AS Max_Updated_At FROM neighborhood_cluster;
END
GO

-- ============================================================
-- 3-DAY RELEVANCE CONFIRMATION
-- ============================================================
-- After T+3 days, the reporter is prompted (push + in-app modal) to confirm
-- whether their event is still relevant. Three responses:
--   relevant     → End_Date is extended +3 days, snooze counter reset
--   not_relevant → Is_Closed = 1, event hidden from public read endpoints
--   snoozed      → Consecutive_Snooze_Count++; on the second snooze in a row
--                  the event is auto-closed
--
-- Skipped when End_Date is set and still in the future.
-- The column additions live next to the [event] table above so that earlier
-- SPs (sp_GetEventsByLocation, sp_GetNearbyEvents) can reference Is_Closed
-- on first deploy.
-- ============================================================

CREATE OR ALTER PROCEDURE sp_GetEventsNeedingConfirmation
AS
BEGIN
    SET NOCOUNT ON;
    -- One row per (event, device token) for direct FCM fan-out.
    -- Predicate: open, created 3+ days ago, no current/future End_Date,
    -- and either never prompted or last prompted 3+ days ago.
    SELECT
        e.Event_ID,
        e.User_ID,
        e.Description,
        e.Created_At,
        e.Last_Confirmation_Prompt_At,
        c.City_Name,
        s.Street_Name,
        l.House_Number,
        l.Latitude  AS Lat,
        l.Longitude AS Lng,
        dt.Token,
        dt.Platform
    FROM event e
    INNER JOIN location l ON l.Location_ID = e.Location_ID
    INNER JOIN city c ON c.City_ID = l.City_ID
    LEFT JOIN streets s ON s.Street_ID = l.Street_ID
    LEFT JOIN device_token dt ON dt.User_ID = e.User_ID
    WHERE e.Is_Closed = 0
      AND DATEDIFF(day, e.Created_At, SYSUTCDATETIME()) >= 3
      AND (e.End_Date IS NULL OR e.End_Date < SYSUTCDATETIME())
      AND (e.Last_Confirmation_Prompt_At IS NULL
           OR DATEDIFF(day, e.Last_Confirmation_Prompt_At, SYSUTCDATETIME()) >= 3);
END
GO

CREATE OR ALTER PROCEDURE sp_GetPendingConfirmationsForUser
    @User_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        e.Event_ID,
        e.Description,
        e.Created_At,
        e.EventStatus,
        et.Name AS EventType_Name,
        c.City_Name,
        s.Street_Name,
        l.House_Number
    FROM event e
    INNER JOIN event_type et ON e.EventType_ID = et.EventType_ID
    INNER JOIN location l ON l.Location_ID = e.Location_ID
    INNER JOIN city c ON c.City_ID = l.City_ID
    LEFT JOIN streets s ON s.Street_ID = l.Street_ID
    WHERE e.User_ID = @User_ID
      AND e.Is_Closed = 0
      AND DATEDIFF(day, e.Created_At, SYSUTCDATETIME()) >= 3
      AND (e.End_Date IS NULL OR e.End_Date < SYSUTCDATETIME())
    ORDER BY e.Created_At ASC;
END
GO

CREATE OR ALTER PROCEDURE sp_MarkConfirmationPromptSent
    @Event_ID INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE event
    SET Last_Confirmation_Prompt_At = SYSUTCDATETIME()
    WHERE Event_ID = @Event_ID;
END
GO

CREATE OR ALTER PROCEDURE sp_RecordRelevanceResponse
    @Event_ID INT,
    @User_ID  INT,
    @Response NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    IF @Response NOT IN (N'relevant', N'not_relevant', N'snoozed')
    BEGIN
        THROW 50001, 'Invalid response value', 1;
    END

    DECLARE @CurrentSnoozes INT;
    DECLARE @OwnedByUser BIT = 0;

    SELECT @OwnedByUser = 1, @CurrentSnoozes = Consecutive_Snooze_Count
    FROM event
    WHERE Event_ID = @Event_ID AND User_ID = @User_ID AND Is_Closed = 0;

    IF @OwnedByUser = 0
    BEGIN
        SELECT CAST(0 AS BIT) AS Closed, CAST(NULL AS DATETIME) AS NewEndDate, CAST(0 AS BIT) AS Updated;
        RETURN;
    END

    DECLARE @NewClosed BIT = 0;
    DECLARE @NewEndDate DATETIME = NULL;

    IF @Response = N'relevant'
    BEGIN
        SET @NewEndDate = DATEADD(day, 3, SYSUTCDATETIME());
        UPDATE event
        SET Last_Confirmation_Response = @Response,
            Consecutive_Snooze_Count = 0,
            End_Date = @NewEndDate,
            Last_Confirmation_Prompt_At = SYSUTCDATETIME()
        WHERE Event_ID = @Event_ID;
    END
    ELSE IF @Response = N'not_relevant'
    BEGIN
        SET @NewClosed = 1;
        UPDATE event
        SET Last_Confirmation_Response = @Response,
            Is_Closed = 1,
            Last_Confirmation_Prompt_At = SYSUTCDATETIME()
        WHERE Event_ID = @Event_ID;
    END
    ELSE -- snoozed
    BEGIN
        DECLARE @NewCount INT = ISNULL(@CurrentSnoozes, 0) + 1;
        IF @NewCount >= 2
        BEGIN
            SET @NewClosed = 1;
            UPDATE event
            SET Last_Confirmation_Response = N'not_relevant',
                Consecutive_Snooze_Count = @NewCount,
                Is_Closed = 1,
                Last_Confirmation_Prompt_At = SYSUTCDATETIME()
            WHERE Event_ID = @Event_ID;
        END
        ELSE
        BEGIN
            UPDATE event
            SET Last_Confirmation_Response = @Response,
                Consecutive_Snooze_Count = @NewCount,
                Last_Confirmation_Prompt_At = SYSUTCDATETIME()
            WHERE Event_ID = @Event_ID;
        END
    END

    SELECT @NewClosed AS Closed, @NewEndDate AS NewEndDate, CAST(1 AS BIT) AS Updated;
END
GO

-- ============================================================
-- SP: sp_ConfirmEventOngoing  (community "עדיין קורה" confirmation)
-- ============================================================
-- Any authenticated user — not just the original reporter — can confirm that
-- an open report is still happening on the ground. This closes the gap where
-- long-running construction drifted into the client's history section: the
-- reporter's "relevant" reply only extends End_Date by 3 days, so unless they
-- keep answering the push every 3 days the event's End_Date lapses and the
-- UI retires it even though Is_Closed = 0.
--
-- Guards:
--   * Event must be open (Is_Closed = 0).
--   * EventStatus 'קרה' is genuinely historical — not revivable.
--   * End_Date must exist AND have passed. This both matches the only UI
--     state that offers the button (the "לא עודכן" card) and prevents abuse:
--     a confirm can never SHORTEN a future End_Date, and can never ADD an
--     End_Date to an open-ended report.
--
-- The extension is 7 days (vs. the reporter loop's 3) because a community
-- confirmation is a stronger liveness signal than the reporter not having
-- closed it, and it halves the nag cadence for long construction.
-- Consecutive_Snooze_Count resets so a live-confirmed event isn't one snooze
-- away from auto-close.

CREATE OR ALTER PROCEDURE sp_ConfirmEventOngoing
    @Event_ID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NewEndDate DATETIME = DATEADD(day, 7, SYSUTCDATETIME());

    UPDATE event
    SET End_Date = @NewEndDate,
        Consecutive_Snooze_Count = 0
    WHERE Event_ID = @Event_ID
      AND ISNULL(Is_Closed, 0) = 0
      AND (EventStatus IS NULL OR EventStatus <> N'קרה')
      AND End_Date IS NOT NULL
      AND End_Date < SYSUTCDATETIME();

    SELECT CASE WHEN @@ROWCOUNT > 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS Updated,
           @NewEndDate AS NewEndDate;
END
GO

-- ============================================================
-- SP: sp_InsertAuditLog  (security audit trail write path)
-- ============================================================
-- Called by AuditDAL.InsertAuditLogAsync. Single-table insert — no
-- transaction wrapper needed. Truncation guards keep an oversized
-- User-Agent or action string from failing the write (the audit trail
-- must be as hard to break as possible).

CREATE OR ALTER PROCEDURE sp_InsertAuditLog
    @User_ID       INT = NULL,
    @Action        NVARCHAR(200),
    @IP_Address    NVARCHAR(100) = NULL,
    @User_Agent    NVARCHAR(1000) = NULL,
    @Metadata_JSON NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO audit_log (User_ID, Action, IP_Address, User_Agent, Metadata_JSON)
    VALUES (
        @User_ID,
        LEFT(@Action, 100),
        LEFT(@IP_Address, 45),
        LEFT(@User_Agent, 400),
        @Metadata_JSON
    );
END
GO