-- PDFBIZZ starter schema for cPanel/phpMyAdmin.
-- Import into a NEW, empty MySQL/MariaDB database.
-- Laravel's vendor/framework files are still required for the application to run.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  email_verified_at TIMESTAMP NULL DEFAULT NULL,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'user',
  remember_token VARCHAR(100) NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id), UNIQUE KEY users_email_unique (email), KEY users_role_index (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  email VARCHAR(255) NOT NULL,
  token VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(60) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id), UNIQUE KEY categories_slug_unique (slug), KEY categories_active_index (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tools (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(160) NOT NULL,
  short_description VARCHAR(255) NOT NULL,
  description TEXT NULL,
  icon VARCHAR(60) NULL,
  input_formats VARCHAR(255) NULL,
  output_format VARCHAR(30) NULL,
  processing_key VARCHAR(80) NULL,
  seo_title VARCHAR(70) NULL,
  meta_description VARCHAR(170) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id), UNIQUE KEY tools_slug_unique (slug),
  KEY tools_category_active_index (category_id,is_active),
  KEY tools_active_index (is_active), KEY tools_featured_index (is_featured),
  CONSTRAINT tools_category_id_foreign FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversion_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  guest_token_hash CHAR(64) NULL,
  tool_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'queued',
  error_code VARCHAR(80) NULL,
  error_message TEXT NULL,
  started_at TIMESTAMP NULL DEFAULT NULL,
  completed_at TIMESTAMP NULL DEFAULT NULL,
  expires_at TIMESTAMP NULL DEFAULT NULL,
  ip_hash CHAR(64) NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id), KEY conversion_jobs_guest_token_hash_index (guest_token_hash),
  KEY conversion_jobs_status_index (status), KEY conversion_jobs_expires_at_index (expires_at),
  KEY conversion_jobs_user_created_index (user_id,created_at),
  CONSTRAINT conversion_jobs_user_id_foreign FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT conversion_jobs_tool_id_foreign FOREIGN KEY (tool_id) REFERENCES tools(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversion_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversion_job_id BIGINT UNSIGNED NOT NULL,
  disk VARCHAR(40) NOT NULL DEFAULT 'local',
  path VARCHAR(500) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  file_role VARCHAR(20) NOT NULL DEFAULT 'input',
  sha256 CHAR(64) NULL,
  created_at TIMESTAMP NULL DEFAULT NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id), KEY conversion_files_job_role_index (conversion_job_id,file_role),
  CONSTRAINT conversion_files_job_id_foreign FOREIGN KEY (conversion_job_id) REFERENCES conversion_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial categories and unpublished tool catalogue. Enable tools only after
-- their real processing handler has been installed and tested.
INSERT INTO categories (name,slug,description,icon,is_active,sort_order,created_at,updated_at) VALUES
('Convert PDF','convert-pdf','Convert PDF files to and from common document and image formats.','file-conversion',1,1,NOW(),NOW()),
('Organize PDF','organize-pdf','Merge, split, rotate and rearrange PDF pages.','files',1,2,NOW(),NOW()),
('Edit PDF','edit-pdf','Add text, images, watermarks and page numbers.','edit',1,3,NOW(),NOW()),
('PDF Security','pdf-security','Protect documents and manage PDF metadata.','shield',1,4,NOW(),NOW()),
('Advanced PDF','advanced-pdf','OCR, extraction and advanced document utilities.','settings',1,5,NOW(),NOW())
ON DUPLICATE KEY UPDATE name=VALUES(name), is_active=VALUES(is_active);

INSERT INTO tools (category_id,name,slug,short_description,description,icon,input_formats,output_format,processing_key,seo_title,meta_description,is_active,is_featured,sort_order,created_at,updated_at)
SELECT c.id,v.name,v.slug,v.short_description,v.description,v.icon,v.input_formats,v.output_format,v.processing_key,v.seo_title,v.meta_description,1,v.is_featured,v.sort_order,NOW(),NOW()
FROM (
 SELECT 'Convert PDF' category,'PDF to Word' name,'pdf-to-word' slug,'Convert PDF documents into editable Word files.' short_description,'PDF to Word conversion. Processing handler must be installed and tested before activation.' description,'file-word' icon,'PDF' input_formats,'DOCX' output_format,'pdf_to_word' processing_key,'Convert PDF to Word Online Free' seo_title,'Convert PDF documents to editable Word files with an online PDF toolkit.' meta_description,0 is_featured,1 sort_order UNION ALL
 SELECT 'Convert PDF','PDF to JPG','pdf-to-jpg','Convert PDF pages into JPG images.','Render PDF pages as JPG images after a compatible renderer is installed.','image','PDF','JPG','pdf_to_jpg','PDF to JPG Online Free','Convert PDF pages into JPG images online.',1,2 UNION ALL
 SELECT 'Convert PDF','PDF to PNG','pdf-to-png','Convert PDF pages into PNG images.','Render PDF pages as PNG images after a compatible renderer is installed.','image','PDF','PNG','pdf_to_png','PDF to PNG Online Free','Convert PDF pages into PNG images online.',0,3 UNION ALL
 SELECT 'Convert PDF','PDF to Text','pdf-to-text','Extract selectable text from a PDF.','Extract text from supported PDFs. Scanned documents require OCR.','file-text','PDF','TXT','pdf_to_text','Extract Text from PDF Online','Extract text from PDF documents online.',0,4 UNION ALL
 SELECT 'Convert PDF','Word to PDF','word-to-pdf','Convert Word documents into PDF files.','Requires a compatible office conversion engine.','file-pdf','DOCX','PDF','word_to_pdf','Word to PDF Converter Online','Convert Word documents to PDF.',0,5 UNION ALL
 SELECT 'Convert PDF','JPG to PDF','jpg-to-pdf','Turn JPG images into a PDF document.','Create a PDF from uploaded JPG images.','images','JPG','PDF','jpg_to_pdf','JPG to PDF Online Free','Convert JPG images into a PDF document.',0,6 UNION ALL
 SELECT 'Convert PDF','PNG to PDF','png-to-pdf','Turn PNG images into a PDF document.','Create a PDF from uploaded PNG images.','images','PNG','PDF','png_to_pdf','PNG to PDF Online Free','Convert PNG images into a PDF document.',0,7 UNION ALL
 SELECT 'Organize PDF','Merge PDF','merge-pdf','Combine multiple PDF files into one document.','Merge multiple PDFs in the selected order. Requires a tested PDF manipulation library.','files','PDF','PDF','merge_pdf','Merge PDF Files Online Free','Combine multiple PDF files into one document.',1,1 UNION ALL
 SELECT 'Organize PDF','Split PDF','split-pdf','Separate a PDF into individual files or page ranges.','Split a PDF by page ranges after a compatible PDF library is installed.','scissors','PDF','PDF','split_pdf','Split PDF Online Free','Split PDF files into pages or page ranges.',0,2 UNION ALL
 SELECT 'Organize PDF','Rotate PDF','rotate-pdf','Rotate PDF pages to the correct orientation.','Rotate selected PDF pages after a compatible PDF library is installed.','rotate-cw','PDF','PDF','rotate_pdf','Rotate PDF Pages Online','Rotate PDF pages to the correct orientation.',0,3 UNION ALL
 SELECT 'Organize PDF','Extract PDF Pages','extract-pdf-pages','Save selected pages as a new PDF.','Extract selected pages from an uploaded PDF.','copy','PDF','PDF','extract_pages','Extract PDF Pages Online','Extract selected pages from a PDF document.',0,4 UNION ALL
 SELECT 'Organize PDF','Delete PDF Pages','delete-pdf-pages','Remove unwanted pages from a PDF.','Delete selected pages after a compatible PDF library is installed.','trash','PDF','PDF','delete_pages','Delete PDF Pages Online','Remove unwanted pages from PDF documents.',0,5 UNION ALL
 SELECT 'Edit PDF','Watermark PDF','watermark-pdf','Add a text watermark to PDF pages.','Apply a text watermark after a compatible PDF library is installed.','stamp','PDF','PDF','watermark_pdf','Add Watermark to PDF Online','Add a watermark to PDF pages.',0,1 UNION ALL
 SELECT 'Edit PDF','Add Page Numbers','add-page-numbers','Insert page numbers into a PDF document.','Add page numbers after a compatible PDF library is installed.','list-ordered','PDF','PDF','page_numbers','Add Page Numbers to PDF Online','Add page numbers to PDF documents.',0,2 UNION ALL
 SELECT 'PDF Security','Protect PDF','protect-pdf','Password-protect a PDF document.','Encrypt PDFs using a compatible, secure PDF processing engine.','lock','PDF','PDF','protect_pdf','Protect PDF with Password Online','Password-protect PDF documents.',0,1 UNION ALL
 SELECT 'PDF Security','Remove PDF Metadata','remove-pdf-metadata','Remove metadata from a PDF document.','Remove metadata while preserving document content where supported.','shield-check','PDF','PDF','remove_metadata','Remove PDF Metadata Online','Remove metadata from PDF documents.',0,2
) v JOIN categories c ON c.slug=REPLACE(LOWER(v.category),' ','-')
ON DUPLICATE KEY UPDATE name=VALUES(name),short_description=VALUES(short_description),description=VALUES(description),is_active=1;

SET FOREIGN_KEY_CHECKS=1;