-- Create businesses table
CREATE TABLE businesses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(255) NOT NULL,
    framework_job_name VARCHAR(255) NOT NULL,
    initial_business_value DECIMAL(10,2) NOT NULL,
    postal_location VARCHAR(255) NOT NULL,
    business_type VARCHAR(255) NOT NULL,
    developer VARCHAR(255) NOT NULL,
    date_of_establishment DATE NOT NULL
);

-- Create transactions table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    date_of_sale DATE NOT NULL,
    sale_amount DECIMAL(10,2) NOT NULL,
    purchaser_name VARCHAR(255) NOT NULL,
    notes TEXT NOT NULL,
    FOREIGN KEY (business_id) REFERENCES businesses(id)
);

-- Create notes table
CREATE TABLE notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    date DATE NOT NULL,
    staff_member VARCHAR(255) NOT NULL,
    note_comments TEXT NOT NULL,
    image_link VARCHAR(255),
    last_edited_date DATE NOT NULL,
    FOREIGN KEY (business_id) REFERENCES businesses(id)
);

-- Create trigger for deleting associated transactions when a business is deleted
DELIMITER //

CREATE TRIGGER delete_business_transactions
BEFORE DELETE ON businesses
FOR EACH ROW
BEGIN
    DELETE FROM transactions WHERE business_id = OLD.id;
END //

-- Create trigger for deleting associated notes when a business is deleted
CREATE TRIGGER delete_business_notes
BEFORE DELETE ON businesses
FOR EACH ROW
BEGIN
    DELETE FROM notes WHERE business_id = OLD.id;
END //

DELIMITER ;
