invoice/setup.sql at main · codesmithsys/invoice · GitHub

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
CREATE TABLE IF NOT EXISTS `invoices` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `invoice_number` VARCHAR(50) NOT NULL,
    `date_of_issue` DATE NOT NULL,
    `due_date` DATE DEFAULT NULL,
    `from_name` VARCHAR(255) NOT NULL DEFAULT '',
    `from_address` VARCHAR(500) NOT NULL DEFAULT '',
    `from_email` VARCHAR(255) NOT NULL DEFAULT '',
    `from_vat_no` VARCHAR(100) NOT NULL DEFAULT '',
    `from_phone` VARCHAR(100) NOT NULL DEFAULT '',
    `from_account_number` VARCHAR(100) NOT NULL DEFAULT '',
    `from_swift_bic` VARCHAR(100) NOT NULL DEFAULT '',
    `to_name` VARCHAR(255) NOT NULL DEFAULT '',
    `to_address` VARCHAR(500) NOT NULL DEFAULT '',
    `to_email` VARCHAR(255) NOT NULL DEFAULT '',
    `to_vat_no` VARCHAR(100) NOT NULL DEFAULT '',
    `to_phone` VARCHAR(100) NOT NULL DEFAULT '',
    `currency` VARCHAR(10) NOT NULL DEFAULT 'USD',
    `net_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `discount_type` ENUM('percent','amount') NOT NULL DEFAULT 'percent',
    `discount_value` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `gross_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('draft','sent','paid','overdue') NOT NULL DEFAULT 'draft',
    `notes` TEXT,
    `payment_method` VARCHAR(255) NOT NULL DEFAULT '',
    `qr_data` VARCHAR(500) NOT NULL DEFAULT '',
    `qr_description` VARCHAR(255) NOT NULL DEFAULT '',
    `qr_show` TINYINT(1) NOT NULL DEFAULT 0,
    `logo_path` VARCHAR(500) DEFAULT NULL,
    `footer_text` VARCHAR(255) NOT NULL DEFAULT 'Thank you for your business',
    `share_token` VARCHAR(64) DEFAULT NULL UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoice_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `invoice_id` INT NOT NULL,
    `description` VARCHAR(500) NOT NULL,
    `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `sort_order` INT NOT NULL DEFAULT 0,
    FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;