-- Migration: Add users & membership to existing Growlytics database
-- Run this ONLY if you already have an existing growlytics database.
-- For fresh installs, run schema.sql instead.

USE growlytics;

-- Create users table
CREATE TABLE IF NOT EXISTS users (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  display_name     VARCHAR(100) NOT NULL,
  profile_picture  VARCHAR(255),
  totp_secret      VARCHAR(255),
  totp_enabled     TINYINT(1) DEFAULT 0,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Add user_id and is_public to plants (nullable first for migration safety)
ALTER TABLE plants
  ADD COLUMN IF NOT EXISTS user_id  INT           AFTER id,
  ADD COLUMN IF NOT EXISTS is_public TINYINT(1)   DEFAULT 1 AFTER location;

-- Create a default owner account for any existing plants (change email/password as needed)
-- Password here is 'changeme' — log in and change it immediately
INSERT IGNORE INTO users (email, password_hash, display_name)
VALUES ('owner@growlytics.local',
        '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TgxfwNj8KlU3K4jL5dD6h8LVKl.e',
        'Plant Owner');

-- Assign all existing orphaned plants to the default owner
UPDATE plants SET user_id = (SELECT id FROM users LIMIT 1) WHERE user_id IS NULL;

-- Now make user_id NOT NULL and add FK
ALTER TABLE plants MODIFY COLUMN user_id INT NOT NULL;
ALTER TABLE plants ADD CONSTRAINT fk_plants_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
