-- HabitFlow Database Schema
-- Run this file first: mysql -u root -p < schema.sql

CREATE DATABASE IF NOT EXISTS habitflow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE habitflow;

-- USERS TABLE
CREATE TABLE IF NOT EXISTS users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    email       VARCHAR(200) NOT NULL UNIQUE,
    password    VARCHAR(255) DEFAULT NULL,          -- NULL for Google OAuth users
    google_id   VARCHAR(100) DEFAULT NULL UNIQUE,   -- Google sub ID
    avatar      VARCHAR(500) DEFAULT NULL,          -- Google profile pic URL
    provider    ENUM('local','google') DEFAULT 'local',
    is_verified TINYINT(1) DEFAULT 0,
    otp_code    VARCHAR(10) DEFAULT NULL,
    otp_expires DATETIME DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_google_id (google_id)
) ENGINE=InnoDB;

-- HABITS TABLE
CREATE TABLE IF NOT EXISTS habits (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NOT NULL,
    name         VARCHAR(200) NOT NULL,
    zone         ENUM('morning','afternoon','night') NOT NULL DEFAULT 'morning',
    icon         VARCHAR(10) DEFAULT '🎯',
    color        VARCHAR(10) DEFAULT '#e63946',
    category     VARCHAR(50) DEFAULT 'health',
    reminder     TIME DEFAULT NULL,
    notes        TEXT DEFAULT NULL,
    is_active    TINYINT(1) DEFAULT 1,
    sort_order   INT DEFAULT 0,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_zone (zone)
) ENGINE=InnoDB;

-- HABIT LOGS TABLE
CREATE TABLE IF NOT EXISTS habit_logs (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    user_id        INT NOT NULL,
    habit_id       INT NOT NULL,
    log_date       DATE NOT NULL,
    status         ENUM('pending','completed','missed') DEFAULT 'pending',
    completed_time DATETIME DEFAULT NULL,
    notes          TEXT DEFAULT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_log (user_id, habit_id, log_date),
    FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
    FOREIGN KEY (habit_id) REFERENCES habits(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, log_date),
    INDEX idx_habit_id  (habit_id)
) ENGINE=InnoDB;

-- SESSIONS TABLE (server-side session store — optional, used alongside PHP sessions)
CREATE TABLE IF NOT EXISTS user_sessions (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    token      VARCHAR(128) NOT NULL UNIQUE,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

-- PASSWORD RESETS TABLE
CREATE TABLE IF NOT EXISTS password_resets (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(200) NOT NULL,
    otp        VARCHAR(10) NOT NULL,
    expires_at DATETIME NOT NULL,
    used       TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB;