USE indes_billing;
CREATE TABLE IF NOT EXISTS expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 expense_date DATE NOT NULL,
 category VARCHAR(100) NOT NULL,
 description VARCHAR(255) NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_expense_date (expense_date),
 CONSTRAINT fk_expense_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS point_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id INT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NULL,
 points INT NOT NULL,
 type ENUM('earned','redeemed','adjustment') NOT NULL,
 description VARCHAR(255) NOT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_points_invoice (invoice_id),
 INDEX idx_points_customer (customer_id),
 CONSTRAINT fk_points_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 CONSTRAINT fk_points_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
 CONSTRAINT fk_points_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;
INSERT INTO settings(setting_key,setting_value) VALUES ('points_per_on_time_payment','10') ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);
