-- ============================================================
-- Restaurant Time Clock - Database Schema
-- MySQL 8.0+  |  UTF-8mb4
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ------------------------------------------------------------
-- Users  (admins / managers)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(100)  NOT NULL,
  `email`         VARCHAR(150)  NOT NULL UNIQUE,
  `password_hash` VARCHAR(255)  NOT NULL,
  `role`          ENUM('admin','manager') NOT NULL DEFAULT 'manager',
  `active`        TINYINT(1)    NOT NULL DEFAULT 1,
  `last_login_at` DATETIME      NULL,
  `created_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Locations  (e.g. "Main Street", "Drive-Through")
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `locations` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(100) NOT NULL,
  `address`    VARCHAR(255) NULL,
  `active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Employees
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `employees` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id`  INT UNSIGNED NOT NULL,
  `first_name`   VARCHAR(60)  NOT NULL,
  `last_name`    VARCHAR(60)  NOT NULL,
  `email`        VARCHAR(150) NULL,
  `phone`        VARCHAR(30)  NULL,
  `pin_hash`     VARCHAR(255) NOT NULL,
  `hourly_rate`  DECIMAL(7,2) NOT NULL DEFAULT 0.00,
  `position`     VARCHAR(80)  NULL,
  `active`       TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_emp_location` FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- QR Stations  (tablets / kiosks on the wall)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `qr_stations` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `location_id` INT UNSIGNED NOT NULL,
  `name`        VARCHAR(100) NOT NULL,
  `token`       CHAR(64)     NOT NULL UNIQUE,
  `active`      TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_station_location` FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Punches  (every clock-in / out / break event)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `punches` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id`  INT UNSIGNED    NOT NULL,
  `station_id`   INT UNSIGNED    NULL,           -- NULL = manual entry
  `punch_type`   ENUM('clock_in','clock_out','break_start','break_end') NOT NULL,
  `punched_at`   DATETIME        NOT NULL,
  `ip_address`   VARCHAR(45)     NULL,
  `is_manual`    TINYINT(1)      NOT NULL DEFAULT 0,
  `edited_by`    INT UNSIGNED    NULL,           -- user.id who last edited
  `edit_reason`  TEXT            NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_punch_employee` (`employee_id`, `punched_at`),
  INDEX `idx_punch_date` (`punched_at`),
  CONSTRAINT `fk_punch_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`),
  CONSTRAINT `fk_punch_station`  FOREIGN KEY (`station_id`)  REFERENCES `qr_stations`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_punch_editor`   FOREIGN KEY (`edited_by`)   REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Timesheets  (computed daily summaries — populated by cron or on-demand)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `timesheets` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id`     INT UNSIGNED    NOT NULL,
  `work_date`       DATE            NOT NULL,
  `total_minutes`   INT             NOT NULL DEFAULT 0,  -- worked (excl. breaks)
  `break_minutes`   INT             NOT NULL DEFAULT 0,
  `gross_pay`       DECIMAL(9,2)    NOT NULL DEFAULT 0.00,
  `is_finalized`    TINYINT(1)      NOT NULL DEFAULT 0,
  `computed_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_emp_date` (`employee_id`, `work_date`),
  CONSTRAINT `fk_ts_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Time Edit Requests  (manager edits pending admin approval, optional)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `time_edit_requests` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `punch_id`     BIGINT UNSIGNED NOT NULL,
  `requested_by` INT UNSIGNED    NOT NULL,
  `old_time`     DATETIME        NOT NULL,
  `new_time`     DATETIME        NOT NULL,
  `reason`       TEXT            NOT NULL,
  `status`       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by`  INT UNSIGNED    NULL,
  `reviewed_at`  DATETIME        NULL,
  `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ter_punch`     FOREIGN KEY (`punch_id`)     REFERENCES `punches`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ter_requester` FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`),
  CONSTRAINT `fk_ter_reviewer`  FOREIGN KEY (`reviewed_by`)  REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Audit Logs
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED    NULL,
  `action`     VARCHAR(80)     NOT NULL,
  `entity`     VARCHAR(60)     NULL,
  `entity_id`  BIGINT UNSIGNED NULL,
  `old_value`  JSON            NULL,
  `new_value`  JSON            NULL,
  `ip_address` VARCHAR(45)     NULL,
  `user_agent` VARCHAR(255)    NULL,
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_audit_entity`  (`entity`, `entity_id`),
  INDEX `idx_audit_user`    (`user_id`),
  INDEX `idx_audit_created` (`created_at`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- PIN rate-limit tracking
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pin_attempts` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address`  VARCHAR(45)     NOT NULL,
  `station_token` CHAR(64)      NOT NULL,
  `attempted_at` DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_pin_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;
