

-- 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;
