

CREATE TABLE IF NOT EXISTS account_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    account_type ENUM('cash', 'card', 'upi', 'credit', 'bank') NOT NULL,
    account_id INT DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (account_id) REFERENCES bank_accounts(id)
);


ALTER TABLE sales MODIFY payment_method ENUM('cash','card','upi','credit','bank') DEFAULT 'cash';


ALTER TABLE expenses 
ADD COLUMN payment_method ENUM('cash', 'card', 'upi', 'bank') DEFAULT 'cash',
ADD COLUMN bank_account_id INT NULL,
ADD FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id);


ALTER TABLE account_transactions 
MODIFY sale_id INT NULL;

CREATE TABLE IF NOT EXISTS hold_items (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    hold_id INT NULL,
    item_id INT NULL,
    item_name VARCHAR(100) NULL,
    quantity INT NULL,
    unit_price DECIMAL(10,2) NULL,
    tax_rate DECIMAL(5,2) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
