CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL, business_name VARCHAR(180) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL,
 phone VARCHAR(40) NULL, address TEXT NULL, logo VARCHAR(255) NULL,
 currency VARCHAR(8) NOT NULL DEFAULT 'NGN', tax_number VARCHAR(80) NULL,
 payment_details TEXT NULL, brand_color VARCHAR(20) NOT NULL DEFAULT '#4f46e5',
 plan VARCHAR(40) NOT NULL DEFAULT 'Starter', subscription_status VARCHAR(30) NOT NULL DEFAULT 'inactive',
 subscription_expires_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(180) NOT NULL, email VARCHAR(190) NULL, phone VARCHAR(40) NULL,
 address TEXT NULL, tax_number VARCHAR(80) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id), CONSTRAINT fk_customers_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 customer_id BIGINT UNSIGNED NOT NULL, invoice_number VARCHAR(60) NOT NULL,
 issue_date DATE NOT NULL, due_date DATE NULL, type ENUM('invoice','quote','receipt') NOT NULL DEFAULT 'invoice',
 status VARCHAR(30) NOT NULL DEFAULT 'Sent', notes TEXT NULL, subtotal DECIMAL(13,2) NOT NULL DEFAULT 0,
 discount DECIMAL(13,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(6,3) NOT NULL DEFAULT 0,
 tax_amount DECIMAL(13,2) NOT NULL DEFAULT 0, total DECIMAL(13,2) NOT NULL DEFAULT 0,
 amount_paid DECIMAL(13,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_owner_number(user_id, invoice_number), INDEX(user_id),
 CONSTRAINT fk_invoices_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_invoices_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL,
 description VARCHAR(255) NOT NULL, quantity DECIMAL(12,2) NOT NULL DEFAULT 1,
 unit_price DECIMAL(13,2) NOT NULL DEFAULT 0, line_total DECIMAL(13,2) NOT NULL DEFAULT 0,
 CONSTRAINT fk_items_invoice FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(13,2) NOT NULL,
 payment_date DATE NOT NULL, method VARCHAR(40) NOT NULL DEFAULT 'Bank Transfer',
 reference VARCHAR(120) NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id), CONSTRAINT fk_payments_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_payments_invoice FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(180) NOT NULL, category VARCHAR(100) NULL, amount DECIMAL(13,2) NOT NULL,
 expense_date DATE NOT NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id), CONSTRAINT fk_expenses_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;