-- ============================================================
-- MikeSPA — Multi-user migration script
-- Run this once via your MySQL console on the server.
--
-- BEFORE YOU RUN:
--   1. Find your WhatsApp number from config.php (whatsapp_to)
--      e.g. 'whatsapp:+447911123456'
--   2. Replace every occurrence of 'whatsapp:+447XXXXXXXXX' below
--      with your actual number.
--   3. Fill in the INSERT into `users` at the bottom.
--   4. Generate your bcrypt password hash with:
--        php -r "echo password_hash('your_password_here', PASSWORD_BCRYPT) . PHP_EOL;"
--      Then run the UPDATE at the bottom to set it.
-- ============================================================

USE mikespa;

-- ── 1. Create users table ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      VARCHAR(50)  NOT NULL,         -- WhatsApp phone e.g. whatsapp:+447911123456
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(200) NOT NULL,
    calendar_id  VARCHAR(200) NOT NULL,         -- Google Calendar ID shared with service account
    pwa_password VARCHAR(255) NULL,             -- bcrypt hash for PWA login; NULL = no PWA access
    active       TINYINT(1)   NOT NULL DEFAULT 1,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_user_id (user_id),
    UNIQUE INDEX idx_email   (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2. lists — add user_id column ────────────────────────────
ALTER TABLE lists
    ADD COLUMN user_id VARCHAR(50) NOT NULL DEFAULT '' AFTER id;

-- Replace the old UNIQUE(slug) with per-user unique(user_id, slug)
ALTER TABLE lists DROP INDEX idx_slug;
ALTER TABLE lists ADD UNIQUE INDEX idx_user_slug (user_id, slug);

-- ── 3. sent_alerts — add user_id column ──────────────────────
ALTER TABLE sent_alerts
    ADD COLUMN user_id VARCHAR(50) NOT NULL DEFAULT '' AFTER id;

ALTER TABLE sent_alerts
    ADD INDEX idx_user (user_id);

-- ── 4. dismissed_events — add user_id, change PK ─────────────
-- The current PK is just event_id; we need (user_id, event_id).
ALTER TABLE dismissed_events
    ADD COLUMN user_id VARCHAR(50) NOT NULL DEFAULT '' AFTER event_id;

ALTER TABLE dismissed_events DROP PRIMARY KEY;
ALTER TABLE dismissed_events ADD PRIMARY KEY (user_id, event_id);

-- ── 5. pending_clarifications — add user_id ──────────────────
ALTER TABLE pending_clarifications
    ADD COLUMN user_id VARCHAR(50) NOT NULL DEFAULT '' AFTER id;

ALTER TABLE pending_clarifications
    ADD INDEX idx_user_pending (user_id, resolved, created_at);

-- ── 6. Backfill existing rows with YOUR WhatsApp number ──────
-- Replace +447XXXXXXXXX with your actual number from config.php
UPDATE lists                  SET user_id = 'whatsapp:+447XXXXXXXXX' WHERE user_id = '';
UPDATE sent_alerts            SET user_id = 'whatsapp:+447XXXXXXXXX' WHERE user_id = '';
UPDATE dismissed_events       SET user_id = 'whatsapp:+447XXXXXXXXX' WHERE user_id = '';
UPDATE pending_clarifications SET user_id = 'whatsapp:+447XXXXXXXXX' WHERE user_id = '';

-- ── 7. Insert your user record ───────────────────────────────
-- Replace all placeholders before running.
INSERT INTO users (user_id, name, email, calendar_id, pwa_password)
VALUES (
    'whatsapp:+447XXXXXXXXX',       -- your WhatsApp number (must match whatsapp_to in config.php)
    'Mike',                          -- your display name
    'mikeb@gotohear.com',            -- your email (used for PWA login)
    'miguel.butler.ink@gmail.com',   -- your Google Calendar ID (from config.php google.calendar_id)
    NULL                             -- set your PWA password hash in the step below
);

-- ── 8. Set your PWA password ──────────────────────────────────
-- Run this on the server to generate a bcrypt hash:
--   php -r "echo password_hash('your_password_here', PASSWORD_BCRYPT) . PHP_EOL;"
--
-- Then paste the hash into this UPDATE:
--   UPDATE users SET pwa_password = '$2y$10$...' WHERE email = 'mikeb@gotohear.com';

-- ── 9. Add a colleague (repeat for each person) ──────────────
-- Their calendar must be shared with the service account first:
--   mikespa@mikespa.iam.gserviceaccount.com  (permission: "Make changes to events")
--
-- INSERT INTO users (user_id, name, email, calendar_id, pwa_password)
-- VALUES (
--     'whatsapp:+447YYYYYYYYY',
--     'Alice',
--     'alice@example.com',
--     'alice@gmail.com',
--     NULL   -- set hash separately
-- );
-- UPDATE users SET pwa_password = '$2y$10$...' WHERE email = 'alice@example.com';
