-- Database Schema for B2B Machinery Marketplace

CREATE DATABASE IF NOT EXISTS machinery_bangladesh;
USE machinery_bangladesh;

-- Companies Table
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    type ENUM('Manufacturing', 'Garments', 'ELECTRICAL lift', 'Road and bridge') NOT NULL,
    logo VARCHAR(255),
    status ENUM('active', 'suspended', 'pending') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users Table (RBAC)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NULL, -- NULL for Super Admin
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'company_admin', 'company_operator') NOT NULL,
    status ENUM('active', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
);

-- Machinery Listings
CREATE TABLE listings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category ENUM('construction', 'industrial', 'agriculture', 'other') NOT NULL,
    type ENUM('buy', 'sell', 'rent') NOT NULL,
    price DECIMAL(10, 2),
    rental_rate_daily DECIMAL(10, 2),
    rental_rate_weekly DECIMAL(10, 2),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Seed Initial Super Admin (Password: admin123)
-- In production, passwords would be hashed.
INSERT INTO users (name, email, password, role) 
VALUES ('Super Admin', 'admin@machinery.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');
