CREATE TABLE IF NOT EXISTS order_item_reporting_dimensions (
    order_item_id BIGINT UNSIGNED PRIMARY KEY,
    category_id BIGINT UNSIGNED NULL,
    category_name_snapshot VARCHAR(160) NULL,
    captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_item_reporting_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_item_reporting_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO order_item_reporting_dimensions (order_item_id,category_id,category_name_snapshot)
SELECT oi.id,mi.category_id,c.name
FROM order_items oi
LEFT JOIN menu_items mi ON mi.id=oi.menu_item_id
LEFT JOIN categories c ON c.id=mi.category_id
ON DUPLICATE KEY UPDATE order_item_id=VALUES(order_item_id);

CREATE TABLE IF NOT EXISTS report_daily_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id BIGINT UNSIGNED NOT NULL,
    metric_date DATE NOT NULL,
    orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    served_orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    cancelled_orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    order_sales DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    order_tax DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reservations_count INT UNSIGNED NOT NULL DEFAULT 0,
    room_service_orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    delayed_kitchen_tickets INT UNSIGNED NOT NULL DEFAULT 0,
    avg_preparation_minutes DECIMAL(10,2) NULL,
    refreshed_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_report_daily_hotel_date (hotel_id,metric_date),
    INDEX idx_report_daily_date (metric_date,hotel_id),
    CONSTRAINT fk_report_daily_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('reports.view','View operational, sales, tax and performance reports'),
('reports.export','Export reports to CSV'),
('audit.view','View audit history and security activity')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN')
  AND p.code IN ('reports.view','reports.export','audit.view');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='READ_ONLY_MANAGER'
  AND p.code IN ('reports.view','audit.view');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('CASHIER','BILLING_STAFF')
  AND p.code='reports.view';

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='IT_ADMINISTRATOR'
  AND p.code='audit.view';