✏ EDIT: database.sql

-- DMS Database schema

-- Roles table
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

-- Permissions table
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

-- Role permissions linking table
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Divisions table
CREATE TABLE IF NOT EXISTS divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

-- Branches table
CREATE TABLE IF NOT EXISTS branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    division_id INT NOT NULL,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    division_id INT DEFAULT NULL,
    branch_id INT DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (division_id) REFERENCES divisions(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- Customers table
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100) DEFAULT NULL,
    aadhar VARCHAR(20) DEFAULT NULL,
    pan VARCHAR(20) DEFAULT NULL,
    gst VARCHAR(20) DEFAULT NULL,
    address TEXT
) ENGINE=InnoDB;

-- Transit table (stock in transit before inward)
CREATE TABLE IF NOT EXISTS transit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_no VARCHAR(50) NOT NULL,
    chassis_number VARCHAR(50) NOT NULL,
    purchase_price DECIMAL(15,2) DEFAULT NULL,
    ndp_price DECIMAL(15,2) DEFAULT NULL,
    nag_number VARCHAR(50) DEFAULT NULL,
    vehicle_model VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','received') DEFAULT 'pending'
) ENGINE=InnoDB;

-- Inward table (stock available in branch)
CREATE TABLE IF NOT EXISTS inward (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transit_id INT NOT NULL,
    engine_number VARCHAR(50) DEFAULT NULL,
    branch_id INT NOT NULL,
    received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('in_stock','allocated','transferred') DEFAULT 'in_stock',
    FOREIGN KEY (transit_id) REFERENCES transit(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- Transfers table (stock movement)
CREATE TABLE IF NOT EXISTS transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inward_id INT NOT NULL,
    from_branch INT NOT NULL,
    to_branch INT NOT NULL,
    requested_by INT NOT NULL,
    approved_by INT DEFAULT NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    status ENUM('requested','approved','completed') DEFAULT 'requested',
    FOREIGN KEY (inward_id) REFERENCES inward(id),
    FOREIGN KEY (from_branch) REFERENCES branches(id),
    FOREIGN KEY (to_branch) REFERENCES branches(id),
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Vehicle bookings table
CREATE TABLE IF NOT EXISTS vehicle_bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_no VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_contact VARCHAR(20) NOT NULL,
    chassis_number VARCHAR(50) DEFAULT NULL,
    vehicle_model VARCHAR(100) DEFAULT NULL,
    branch_id INT NOT NULL,
    created_by INT NOT NULL,
    booking_date DATE DEFAULT CURRENT_DATE,
    status ENUM('draft','submitted','approved','delivered') DEFAULT 'draft',
    total_amount DECIMAL(15,2) DEFAULT 0,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Service bookings table
CREATE TABLE IF NOT EXISTS service_bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_no VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    vehicle_rc VARCHAR(50) DEFAULT NULL,
    complaint TEXT DEFAULT NULL,
    branch_id INT NOT NULL,
    created_by INT NOT NULL,
    booking_date DATE DEFAULT CURRENT_DATE,
    status ENUM('open','in_progress','closed') DEFAULT 'open',
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Targets table (monthly targets for executives)
CREATE TABLE IF NOT EXISTS targets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    target_amount DECIMAL(15,2) DEFAULT NULL,
    target_count INT DEFAULT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Insert initial roles
INSERT INTO roles (name, description) VALUES
 ('dealer_admin','Dealer administrator'),
 ('stock_incharge','Stock in-charge'),
 ('division_manager','Division manager'),
 ('branch_manager','Branch manager'),
 ('sales_executive','Sales executive'),
 ('service_executive','Service executive');

-- Insert initial divisions and branches (sample)
INSERT INTO divisions (name) VALUES ('North Division'),('South Division');

INSERT INTO branches (division_id, code, name) VALUES
 (1, 'ND01','North Branch 1'),
 (1, 'ND02','North Branch 2'),
 (2, 'SD01','South Branch 1'),
 (2, 'SD02','South Branch 2');

-- Insert initial admin user (username: admin, password: admin123)
INSERT INTO users (username, password_hash, name, role_id, division_id, branch_id) VALUES
 ('admin', SHA2('admin123',256), 'System Admin', 1, 1, 1);

[✗] CANCEL