
CREATE TABLE bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bank_name VARCHAR(100) NOT NULL,
    account_title VARCHAR(150) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    iban VARCHAR(50),
    branch_name VARCHAR(100),
    branch_code VARCHAR(50),
    swift_code VARCHAR(50),
    address TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);






CREATE TABLE cus_outstanding (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE cus_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    sale_id INT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    remarks VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO `setting_categories` (`id`, `name`, `code`) VALUES (NULL, 'Sales Orders', 'sales_orders');

INSERT INTO `settings` (`id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) VALUES (NULL, '4', 'Dine In', 'dine_in', 'boolean', '0', '1', 'Enable Dine In for table Orders', '1');

INSERT INTO `settings` (`id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) VALUES (NULL, '4', 'Take Away', 'take_away', 'boolean', '0', '1', 'Enable Take Away orders', '1');

INSERT INTO `settings` (`id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) VALUES (NULL, '4', 'Delivery', 'delivery', 'boolean', '0', '1', 'Delivery Orders', '1');



CREATE TABLE dinein_floors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  is_active TINYINT(1) DEFAULT 1
);


CREATE TABLE dinein_tables (
  id INT AUTO_INCREMENT PRIMARY KEY,
  floor_id INT NOT NULL,
  table_name VARCHAR(50) NOT NULL,
  seats INT DEFAULT 4,
  status ENUM('available','occupied','reserved') DEFAULT 'available',
  is_active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (floor_id) REFERENCES dinein_floors(id)
);


CREATE TABLE delivery_riders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  email VARCHAR(100),
  vehicle_number VARCHAR(50),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE delivery_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  customer_id INT NOT NULL,
  rider_id INT DEFAULT NULL,
  delivery_status ENUM('pending','out_for_delivery','delivered','cancelled') DEFAULT 'pending',
  delivery_address TEXT,
  delivery_time DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sale_id) REFERENCES sales(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (rider_id) REFERENCES delivery_riders(id)
);


ALTER TABLE sales ADD COLUMN table_id INT DEFAULT NULL;



-- Holds the summary of the held order
CREATE TABLE hold_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    table_id INT NULL,
    order_mode VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Holds the line items


ALTER TABLE hold_orders 
  ADD COLUMN total_amount DECIMAL(10,2) DEFAULT 0.00;


ALTER TABLE sales ADD COLUMN order_mode VARCHAR(50) DEFAULT NULL;


