24 lines
906 B
SQL
24 lines
906 B
SQL
CREATE TABLE IF NOT EXISTS quotations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT,
|
|
quotation_date DATE NOT NULL,
|
|
valid_until DATE,
|
|
status ENUM('pending', 'converted', 'expired', 'cancelled') DEFAULT 'pending',
|
|
total_amount DECIMAL(15,3) DEFAULT 0.000,
|
|
vat_amount DECIMAL(15,3) DEFAULT 0.000,
|
|
total_with_vat DECIMAL(15,3) DEFAULT 0.000,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS quotation_items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
quotation_id INT NOT NULL,
|
|
item_id INT NOT NULL,
|
|
quantity DECIMAL(15,2) NOT NULL,
|
|
unit_price DECIMAL(15,3) DEFAULT 0.000,
|
|
total_price DECIMAL(15,3) DEFAULT 0.000,
|
|
FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (item_id) REFERENCES stock_items(id)
|
|
);
|