-- =====================================================
-- inFixCorp Repair System — Database Schema
-- Run this ONCE in phpMyAdmin before going live
-- =====================================================


-- Users table
CREATE TABLE IF NOT EXISTS users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  password   VARCHAR(255) NOT NULL,
  name       VARCHAR(100) NOT NULL,
  role       VARCHAR(20)  NOT NULL DEFAULT 'admin',
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default admin (password: infixcorp2025)
INSERT INTO users (username, password, name, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'admin');
-- NOTE: Change password immediately after first login via Settings

-- Tickets table
CREATE TABLE IF NOT EXISTS tickets (
  id           VARCHAR(20)  NOT NULL PRIMARY KEY,
  token        VARCHAR(32)  NOT NULL UNIQUE,
  fname        VARCHAR(100) NOT NULL DEFAULT 'N/A',
  lname        VARCHAR(100) NOT NULL DEFAULT 'N/A',
  phone        VARCHAR(30)  NOT NULL DEFAULT 'N/A',
  email        VARCHAR(150) NOT NULL DEFAULT 'N/A',
  device       VARCHAR(100) NOT NULL DEFAULT 'N/A',
  model        VARCHAR(100) NOT NULL DEFAULT 'N/A',
  serial       VARCHAR(100) NOT NULL DEFAULT 'N/A',
  service      VARCHAR(50)  NOT NULL DEFAULT 'N/A',
  issue        TEXT         NOT NULL,
  accessories  VARCHAR(255) NOT NULL DEFAULT 'N/A',
  priority     VARCHAR(20)  NOT NULL DEFAULT 'Normal',
  eta          DATE                  DEFAULT NULL,
  status       VARCHAR(30)  NOT NULL DEFAULT 'Received',
  notes        TEXT,
  work_notes   TEXT,
  pending_client TINYINT(1) NOT NULL DEFAULT 0,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Quotes table
CREATE TABLE IF NOT EXISTS quotes (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id    VARCHAR(20)  NOT NULL,
  line_desc    VARCHAR(255) NOT NULL,
  qty          INT          NOT NULL DEFAULT 1,
  price        DECIMAL(10,2) NOT NULL DEFAULT 0,
  sort_order   INT          NOT NULL DEFAULT 0,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Quote meta (notes, approval)
CREATE TABLE IF NOT EXISTS quote_meta (
  ticket_id    VARCHAR(20)  NOT NULL PRIMARY KEY,
  notes        TEXT,
  approved     TINYINT(1)   NOT NULL DEFAULT 0,
  approved_by  VARCHAR(150)          DEFAULT NULL,
  approved_at  DATETIME              DEFAULT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Pickups table
CREATE TABLE IF NOT EXISTS pickups (
  ticket_id    VARCHAR(20)  NOT NULL PRIMARY KEY,
  picked_up_by VARCHAR(150) NOT NULL,
  picked_up_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
  session_id   VARCHAR(128) NOT NULL PRIMARY KEY,
  user_id      INT          NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME     NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Index for token lookups (client tracking)
CREATE INDEX idx_token ON tickets(token);
CREATE INDEX idx_status ON tickets(status);