-- 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
);

-- 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
);
