-- Core PHP API Database Schema
-- Simple schema for e-commerce application

CREATE DATABASE IF NOT EXISTS ashwini_ecommerce;
USE ashwini_ecommerce;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2) DEFAULT 0,
    category_id INT,
    image_url VARCHAR(255),
    stock INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Banners table
CREATE TABLE IF NOT EXISTS banners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    description TEXT,
    image_url VARCHAR(255) NOT NULL,
    link_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Addresses table
CREATE TABLE IF NOT EXISTS addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    address_line_1 VARCHAR(200) NOT NULL,
    address_line_2 VARCHAR(200),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    pincode VARCHAR(10) NOT NULL,
    landmark VARCHAR(100),
    address_type ENUM('home', 'work', 'other') DEFAULT 'home',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Cart table
CREATE TABLE IF NOT EXISTS cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_product (user_id, product_id)
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    address_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    delivery_slot VARCHAR(100),
    notes TEXT,
    status ENUM('placed', 'confirmed', 'preparing', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'placed',
    cancelled_at TIMESTAMP NULL,
    cancel_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE CASCADE
);

-- Order Items table
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Insert sample data

-- Sample users
INSERT INTO users (name, email, phone) VALUES 
('John Doe', 'john@example.com', '+1234567890'),
('Jane Smith', 'jane@example.com', '+1987654321');

-- Sample categories
INSERT INTO categories (name, description, image_url, display_order) VALUES 
('Vegetables', 'Fresh vegetables', 'vegetables.jpg', 1),
('Fruits', 'Fresh fruits', 'fruits.jpg', 2),
('Dairy', 'Dairy products', 'dairy.jpg', 3),
('Grains', 'Rice, wheat, and other grains', 'grains.jpg', 4),
('Spices', 'Spices and condiments', 'spices.jpg', 5);

-- Sample products
INSERT INTO products (name, description, price, discount_price, category_id, image_url, stock) VALUES 
('Fresh Tomatoes', 'Red ripe tomatoes', 50.00, 45.00, 1, 'tomatoes.jpg', 100),
('Bananas', 'Sweet bananas', 60.00, 0, 2, 'bananas.jpg', 50),
('Milk', 'Fresh cow milk', 60.00, 55.00, 3, 'milk.jpg', 30),
('Rice', 'Basmati rice', 80.00, 0, 4, 'rice.jpg', 25),
('Turmeric Powder', 'Pure turmeric powder', 120.00, 110.00, 5, 'turmeric.jpg', 40),
('Onions', 'Fresh onions', 40.00, 35.00, 1, 'onions.jpg', 75),
('Apples', 'Red apples', 150.00, 0, 2, 'apples.jpg', 20),
('Yogurt', 'Fresh yogurt', 45.00, 0, 3, 'yogurt.jpg', 35),
('Wheat Flour', 'Whole wheat flour', 70.00, 65.00, 4, 'wheat_flour.jpg', 15),
('Red Chili Powder', 'Spicy red chili powder', 90.00, 0, 5, 'red_chili.jpg', 60);

-- Sample banners
INSERT INTO banners (title, description, image_url, link_url, display_order) VALUES 
('Fresh Vegetables Sale', 'Get 20% off on all vegetables', 'vegetable_banner.jpg', '/products?category=vegetables', 1),
('Fruits Special Offer', 'Buy 2 get 1 free on fruits', 'fruit_banner.jpg', '/products?category=fruits', 2),
('Dairy Products', 'Fresh dairy products delivered', 'dairy_banner.jpg', '/products?category=dairy', 3);

-- Sample addresses
INSERT INTO addresses (user_id, name, phone, address_line_1, address_line_2, city, state, pincode, landmark, address_type, is_default) VALUES 
(1, 'John Doe', '+1234567890', '123 Main Street', 'Apt 4B', 'Mumbai', 'Maharashtra', '400001', 'Near City Mall', 'home', TRUE),
(1, 'John Doe', '+1234567890', '456 Office Building', 'Floor 2', 'Mumbai', 'Maharashtra', '400002', 'Near Business Park', 'work', FALSE),
(2, 'Jane Smith', '+1987654321', '789 Residential Area', '', 'Delhi', 'Delhi', '110001', 'Near Metro Station', 'home', TRUE);

-- Create indexes for better performance
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_cart_user ON cart(user_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_addresses_user ON addresses(user_id);
