-- Table Management System Migration
-- Add tables table and update transactions table

USE pos_coffee;

-- Create tables table
CREATE TABLE IF NOT EXISTS tables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    capacity INT DEFAULT 4,
    status ENUM('available', 'occupied', 'reserved', 'cleaning') DEFAULT 'available',
    location VARCHAR(100) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_table_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add table_id to transactions table
ALTER TABLE transactions 
ADD COLUMN table_id INT NULL AFTER user_id,
ADD FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE SET NULL;

-- Insert sample tables
INSERT INTO tables (name, capacity, location, status) VALUES 
('Meja 1', 2, 'Area Depan', 'available'),
('Meja 2', 4, 'Area Depan', 'available'),
('Meja 3', 4, 'Area Tengah', 'available'),
('Meja 4', 6, 'Area Tengah', 'available'),
('Meja 5', 2, 'Area Belakang', 'available'),
('Meja 6', 4, 'Area Belakang', 'available'),
('Meja 7', 8, 'Area VIP', 'available'),
('Meja 8', 4, 'Area VIP', 'available');






