-- Merge Tables Feature Migration
-- Add support for merging multiple tables into one transaction

USE pos_coffee;

-- Create transaction_tables junction table for many-to-many relationship
CREATE TABLE IF NOT EXISTS transaction_tables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    table_id INT NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE CASCADE,
    UNIQUE KEY unique_transaction_table (transaction_id, table_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migrate existing table_id to transaction_tables
INSERT INTO transaction_tables (transaction_id, table_id, is_primary)
SELECT id, table_id, TRUE
FROM transactions
WHERE table_id IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM transaction_tables tt 
    WHERE tt.transaction_id = transactions.id AND tt.table_id = transactions.table_id
);






