CREATE TABLE IF NOT EXISTS organizations (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 full_name VARCHAR(150) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS organization_members (
 org_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
 role ENUM('owner','admin','member') NOT NULL DEFAULT 'member',
 PRIMARY KEY (org_id,user_id), FOREIGN KEY(org_id) REFERENCES organizations(id),
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS parties (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL,
 kind ENUM('customer','supplier','other') NOT NULL DEFAULT 'customer',
 gstin VARCHAR(15) DEFAULT NULL, phone VARCHAR(30) DEFAULT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(org_id,id), FOREIGN KEY(org_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS invoices (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL, party_id BIGINT UNSIGNED NOT NULL,
 invoice_no VARCHAR(80) NOT NULL, invoice_date DATE NOT NULL,
 taxable_value DECIMAL(15,2) NOT NULL DEFAULT 0,
 tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_org (org_id,invoice_no), INDEX (org_id,invoice_date),
 FOREIGN KEY(org_id) REFERENCES organizations(id),
 FOREIGN KEY(party_id) REFERENCES parties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS purchase_register (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL, supplier_name VARCHAR(190) NOT NULL,
 supplier_gstin VARCHAR(15) DEFAULT NULL, invoice_no VARCHAR(80) NOT NULL,
 invoice_date DATE NOT NULL, taxable_value DECIMAL(15,2) NOT NULL DEFAULT 0,
 igst DECIMAL(15,2) NOT NULL DEFAULT 0, cgst DECIMAL(15,2) NOT NULL DEFAULT 0,
 sgst DECIMAL(15,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(org_id,invoice_date), FOREIGN KEY(org_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;