-- Status history table for tracking all status changes
CREATE TABLE IF NOT EXISTS Hadj4_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_type ENUM('return', 'shipment') NOT NULL,
    request_id INT NOT NULL,
    old_status VARCHAR(50),
    new_status VARCHAR(50) NOT NULL,
    changed_by INT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (changed_by) REFERENCES Hadj4_users(id)
);

-- Table for return requests
CREATE TABLE IF NOT EXISTS Hadj4_return_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    company VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    status ENUM('En attente', 'Validé', 'Expédié', 'Refusé', 'Livré') DEFAULT 'En attente',
    address TEXT,
    address_complement TEXT,
    postal_code VARCHAR(10),
    city VARCHAR(255),
    comment TEXT,
    rejection_reason TEXT,
    last_updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (last_updated_by) REFERENCES Hadj4_users(id)
);

-- Table for return request items
CREATE TABLE IF NOT EXISTS Hadj4_return_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    return_request_id INT NOT NULL,
    object_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (return_request_id) REFERENCES Hadj4_return_requests(id) ON DELETE CASCADE
);

-- Table for shipment requests
CREATE TABLE IF NOT EXISTS Hadj4_shipment_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    company VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    status ENUM('En attente', 'Validé', 'Expédié', 'Refusé', 'Livré') DEFAULT 'En attente',
    address TEXT NOT NULL,
    address_complement TEXT,
    postal_code VARCHAR(10) NOT NULL,
    city VARCHAR(255) NOT NULL,
    tracking_number VARCHAR(100),
    comment TEXT,
    rejection_reason TEXT,
    last_updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (last_updated_by) REFERENCES Hadj4_users(id)
);

-- Table for shipment items to send
CREATE TABLE IF NOT EXISTS Hadj4_shipment_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shipment_request_id INT NOT NULL,
    object_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    type ENUM('to_ship', 'to_retrieve') NOT NULL,
    FOREIGN KEY (shipment_request_id) REFERENCES Hadj4_shipment_requests(id) ON DELETE CASCADE
);

-- Add indexes for better performance
CREATE INDEX idx_return_status ON Hadj4_return_requests(status);
CREATE INDEX idx_shipment_status ON Hadj4_shipment_requests(status);
CREATE INDEX idx_status_history_request ON Hadj4_status_history(request_type, request_id);
CREATE INDEX idx_return_date ON Hadj4_return_requests(date);
CREATE INDEX idx_shipment_date ON Hadj4_shipment_requests(date);
