-- ============================================================
-- Migration: single-user → multiuser
-- Run ONCE after making a full database backup.
-- ============================================================

-- 1. Create users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(180) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    roles JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Create initial user preserving the current password hash.
--    Login email: jelen@biblio.local  (change to a real email if you prefer)
INSERT IGNORE INTO users (email, password_hash, display_name)
VALUES (
    'jelen@biblio.local',
    '$2y$12$BGFKcYxFr9kaBSiYM4D8h.NwCKqjYysUVghmKZkDyxIp0Sc5Yql7i',
    'Jelen'
);

-- 3. Create user_books table
CREATE TABLE IF NOT EXISTS user_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    rating TINYINT DEFAULT 0 CHECK (rating >= 0 AND rating <= 5),
    reading_date DATE NULL,
    notes TEXT,
    status ENUM('pendiente', 'leyendo', 'leído', 'abandonado') DEFAULT 'pendiente',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_book (user_id, book_id),
    INDEX idx_user_books_user (user_id),
    INDEX idx_user_books_book (book_id),
    INDEX idx_user_books_status (status),
    CONSTRAINT fk_user_books_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_books_book FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Migrate personal data from books → user_books for the initial user
INSERT IGNORE INTO user_books (user_id, book_id, rating, reading_date, notes, status, created_at, updated_at)
SELECT
    (SELECT id FROM users WHERE email = 'jelen@biblio.local'),
    id,
    rating,
    reading_date,
    notes,
    status,
    created_at,
    updated_at
FROM books;

-- 5. Verify before dropping columns:
--    SELECT COUNT(*) FROM books;
--    SELECT COUNT(*) FROM user_books;
--    Both counts should match.

-- 6. Drop personal columns from books (run ONLY after verifying user_books data is correct)
-- ALTER TABLE books DROP COLUMN rating;
-- ALTER TABLE books DROP COLUMN reading_date;
-- ALTER TABLE books DROP COLUMN notes;
-- ALTER TABLE books DROP COLUMN status;