-- PinRepair Empty Database Dump
-- This file contains the complete database structure without any data
-- Use this for fresh installations or to reset the database

-- MariaDB/MySQL dump 10.19  Distrib 10.11.11-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: pinrepair
-- ------------------------------------------------------


--
-- Database: `pinrepair`
--

CREATE DATABASE IF NOT EXISTS `pinrepair` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `pinrepair`;

--
-- Table structure for table `debug_config`
--

CREATE TABLE `debug_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `is_enabled` tinyint(1) DEFAULT 0,
  `log_level` enum('error','warning','info','debug') DEFAULT 'error',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `errors`
--

CREATE TABLE `errors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pinball_id` int(11) NOT NULL,
  `reporter_id` int(11) NOT NULL,
  `technician_id` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `media_path` varchar(255) DEFAULT NULL,
  `is_urgent` tinyint(1) DEFAULT 0,
  `status` enum('pending','resolved') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `resolved_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pinball_id` (`pinball_id`),
  KEY `reporter_id` (`reporter_id`),
  KEY `technician_id` (`technician_id`),
  CONSTRAINT `errors_ibfk_1` FOREIGN KEY (`pinball_id`) REFERENCES `pinballs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `errors_ibfk_2` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `errors_ibfk_3` FOREIGN KEY (`technician_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `pinballs`
--

CREATE TABLE `pinballs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `manufacturer` varchar(255) NOT NULL,
  `year` int(11) DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `group_id` char(1) NOT NULL DEFAULT 'A',
  `status` enum('active','inactive') DEFAULT 'active',
  `opdb_id` varchar(255) DEFAULT NULL,
  `opdb_text` text DEFAULT NULL,
  `opdb_display` text DEFAULT NULL,
  `error_count` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `status` (`status`),
  KEY `opdb_id` (`opdb_id`),
  CONSTRAINT `pinballs_chk_1` CHECK (`group_id` in ('A','B','C','D','E','F','G','H','I','J'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



--
-- Insert default debug configuration
--

INSERT INTO `debug_config` (`is_enabled`, `log_level`) VALUES
(0, 'error');

-- Indexes for pinballs table
CREATE INDEX idx_pinballs_status_group ON pinballs(status, group_id);
CREATE INDEX idx_pinballs_manufacturer ON pinballs(manufacturer);
CREATE INDEX idx_pinballs_year ON pinballs(year);
CREATE INDEX idx_pinballs_opdb_id ON pinballs(opdb_id);

-- Indexes for errors table
CREATE INDEX idx_errors_status_created ON errors(status, created_at);
CREATE INDEX idx_errors_urgent_status ON errors(is_urgent, status);
CREATE INDEX idx_errors_technician_status ON errors(technician_id, status);


--
-- Create views for common queries
--

-- View for pinball statistics
CREATE VIEW pinball_stats AS
SELECT 
    group_id,
    COUNT(*) as total_pinballs,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_pinballs,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_pinballs,
    SUM(error_count) as total_errors,
    SUM(CASE WHEN opdb_id IS NOT NULL THEN 1 ELSE 0 END) as opdb_linked_pinballs
FROM pinballs 
GROUP BY group_id;

-- View for OPDB integration statistics
CREATE VIEW opdb_stats AS
SELECT 
    COUNT(*) as total_pinballs,
    SUM(CASE WHEN opdb_id IS NOT NULL THEN 1 ELSE 0 END) as opdb_linked_pinballs,
    SUM(CASE WHEN opdb_id IS NULL THEN 1 ELSE 0 END) as opdb_unlinked_pinballs,
    ROUND((SUM(CASE WHEN opdb_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) as opdb_link_percentage
FROM pinballs;

--
-- Create stored procedures
--

DELIMITER $$
END$$
END$$


-- Procedure to update OPDB data for a pinball
CREATE PROCEDURE UpdatePinballOPDBData(
    IN pinball_id INT,
    IN opdb_id VARCHAR(255),
    IN opdb_text TEXT,
    IN opdb_display TEXT
)
BEGIN
    UPDATE pinballs 
    SET 
        opdb_id = opdb_id,
        opdb_text = opdb_text,
        opdb_display = opdb_display,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = pinball_id;
END$$

DELIMITER ;

--
-- Create triggers
--

-- Trigger to update pinball error count when error is created
DELIMITER $$
CREATE TRIGGER tr_errors_after_insert
AFTER INSERT ON errors
FOR EACH ROW
BEGIN
    CALL UpdatePinballErrorCount(NEW.pinball_id);
END$$

-- Trigger to update pinball error count when error is updated
CREATE TRIGGER tr_errors_after_update
AFTER UPDATE ON errors
FOR EACH ROW
BEGIN
    CALL UpdatePinballErrorCount(NEW.pinball_id);
    IF OLD.pinball_id != NEW.pinball_id THEN
        CALL UpdatePinballErrorCount(OLD.pinball_id);
    END IF;
END$$

-- Trigger to update pinball error count when error is deleted
CREATE TRIGGER tr_errors_after_delete
AFTER DELETE ON errors
FOR EACH ROW
BEGIN
    CALL UpdatePinballErrorCount(OLD.pinball_id);
END$$
DELIMITER ;

--
-- Restore original settings
--


--
--

-- This is an empty database dump for PinRepair
-- All tables are created with proper structure but no data
-- Includes OPDB integration fields (opdb_id, opdb_text, opdb_display)
-- Use this for fresh installations or to reset the database
-- 
-- Features included:
-- - Core table structure (users, pinballs, errors, debug_config, password_resets, notification_queue)
-- - OPDB integration fields in pinballs table
-- - Foreign key constraints and indexes
-- - Views for statistics and reporting
-- - Stored procedures for common operations
-- - Triggers for automatic updates
-- - Default debug configuration
-- 
-- Tables included:
-- - users: User management with roles and groups
-- - pinballs: Pinball machine management with OPDB integration
-- - errors: Error reporting and tracking
-- - debug_config: Debug mode configuration
-- - password_resets: Password reset token management
-- - notification_queue: Email notification queue for cronjobs
-- 
-- To import this dump:
-- mysql -u username -p database_name < empty_database.sql
--
-- To create a new database and import:
-- mysql -u username -p -e "CREATE DATABASE pinrepair CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
-- mysql -u username -p pinrepair < empty_database.sql
