Spaces:
Running
Running
File size: 1,591 Bytes
a6ca940 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
```sql
CREATE TABLE clients (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
inn VARCHAR(20),
contact_person VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(255),
address TEXT,
comments TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE suppliers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
inn VARCHAR(20),
contact_person VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(255),
address TEXT,
comments TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE deals (
id INT AUTO_INCREMENT PRIMARY KEY,
deal_number VARCHAR(20) NOT NULL UNIQUE,
client_id INT NOT NULL,
deal_date DATE NOT NULL,
status ENUM('active', 'pending', 'completed', 'cancelled') DEFAULT 'active',
total_purchase DECIMAL(12,2),
total_sale DECIMAL(12,2),
margin DECIMAL(5,2),
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (client_id) REFERENCES clients(id)
);
CREATE TABLE deal_items (
id INT AUTO_INCREMENT PRIMARY KEY,
deal_id INT NOT NULL,
item_number INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
purchase_price DECIMAL(12,2) NOT NULL,
purchase_total DECIMAL(12,2) NOT NULL,
margin DECIMAL(5,2) NOT NULL,
sale_price DECIMAL(12,2) NOT NULL,
sale_total DECIMAL(12,2) NOT NULL,
supplier_id INT,
delivered BOOLEAN DEFAULT FALSE,
FOREIGN KEY (deal_id) REFERENCES deals(id),
FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);
``` |