-- ============================================================
-- FlipTrack Database Schema
-- Run this in phpMyAdmin or MySQL CLI
-- ============================================================

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

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100)        NOT NULL,
    email      VARCHAR(191)        NOT NULL UNIQUE,
    password   VARCHAR(255)        NOT NULL,
    created_at TIMESTAMP           DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP           DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Categories table (seed data)
CREATE TABLE IF NOT EXISTS categories (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT IGNORE INTO categories (name) VALUES
('Cars'), ('Phones'), ('Electronics'), ('Clothing'),
('Furniture'), ('Tools'), ('Shoes'), ('Watches'), ('Computers'), ('Other');

-- Items table
CREATE TABLE IF NOT EXISTS items (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id        INT UNSIGNED        NOT NULL,
    name           VARCHAR(150)        NOT NULL,
    category       VARCHAR(80)         NOT NULL DEFAULT 'Other',
    purchase_price DECIMAL(12,2)       NOT NULL DEFAULT 0.00,
    expenses       DECIMAL(12,2)       NOT NULL DEFAULT 0.00,
    selling_price  DECIMAL(12,2)                DEFAULT NULL,
    status         ENUM('Available','Sold','Pending','Loss') NOT NULL DEFAULT 'Available',
    profit         DECIMAL(12,2)                DEFAULT NULL,
    roi            DECIMAL(8,2)                 DEFAULT NULL,
    date_bought    DATE                NOT NULL,
    date_sold      DATE                         DEFAULT NULL,
    notes          TEXT                         DEFAULT NULL,
    image_path     VARCHAR(255)                 DEFAULT NULL,
    created_at     TIMESTAMP           DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP           DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_items_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;