21 lines
752 B
SQL
21 lines
752 B
SQL
CREATE TABLE IF NOT EXISTS invoices (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT,
|
|
invoice_date DATE NOT NULL,
|
|
type ENUM('sale', 'purchase') NOT NULL,
|
|
total_amount DECIMAL(15, 2) DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoice_items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
invoice_id INT NOT NULL,
|
|
item_id INT NOT NULL,
|
|
quantity DECIMAL(15, 2) NOT NULL,
|
|
unit_price DECIMAL(15, 2) NOT NULL,
|
|
total_price DECIMAL(15, 2) NOT NULL,
|
|
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (item_id) REFERENCES stock_items(id) ON DELETE CASCADE
|
|
);
|