-- ============================================================================
-- Green Submissions — database schema
-- Submission Manager by Glenn Lyvers, provided freely through greensubmissions.com
--
-- The web installer imports this file automatically. To install by hand,
-- import it into an empty MySQL/MariaDB database (phpMyAdmin works fine).
-- ============================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- Staff accounts (admins, editors, volunteer readers)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_editors` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_type` enum('superadmin','admin','editor','reader','pending') NOT NULL,
  `date_added` datetime NOT NULL,
  `phone` varchar(100) NOT NULL DEFAULT '',
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Writer accounts
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_poets` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `pending_email` varchar(255) DEFAULT NULL,
  `email_change_token` varchar(64) DEFAULT NULL,
  `email_change_expiry` datetime DEFAULT NULL,
  `author_name` varchar(255) NOT NULL,
  `author_lastname` varchar(255) NOT NULL,
  `pen_name` varchar(255) DEFAULT NULL,
  `signup_date` datetime NOT NULL,
  `email_confirmed` tinyint(1) NOT NULL DEFAULT 0,
  `show_accepted_message` tinyint(1) NOT NULL DEFAULT 1,
  `bio` varchar(1000) NOT NULL DEFAULT '',
  `no_authors_page` tinyint(1) NOT NULL DEFAULT 0,
  `reset_token` varchar(64) DEFAULT NULL,
  `token_expiry` datetime DEFAULT NULL,
  `confirm_token` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reset_token` (`reset_token`),
  KEY `email_change_token` (`email_change_token`),
  KEY `confirm_token` (`confirm_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Editions (issues) and contests
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_editions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `editorid` bigint(20) NOT NULL,
  `edition` varchar(255) NOT NULL,
  `description` mediumtext NOT NULL,
  `is_open` tinyint(1) NOT NULL DEFAULT 0,
  `is_contest` tinyint(1) NOT NULL DEFAULT 0,
  `fee_normal` decimal(8,2) NOT NULL DEFAULT 0.00,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Submissions
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_submissions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `poetid` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` mediumtext NOT NULL,
  `attachment` varchar(255) NOT NULL DEFAULT '',
  `bio_comments` mediumtext NOT NULL,
  `date_submitted` datetime NOT NULL,
  `status` int(11) NOT NULL DEFAULT 0,
  `editionid` int(11) NOT NULL,
  `ip_address` varchar(100) NOT NULL DEFAULT '',
  `type` varchar(100) NOT NULL DEFAULT 'Poetry',
  `printconfirmation` tinyint(1) DEFAULT NULL,
  `ptype` varchar(100) NOT NULL DEFAULT 'free',
  PRIMARY KEY (`id`),
  KEY `poetid` (`poetid`),
  KEY `editionid` (`editionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Status labels. 0 = Pending (implicit), 1 = Accepted, 2 = Rejected.
-- Ids greater than 2 are custom statuses the admin can add/remove in the
-- Editorial Suite. Id 3 is used by the revision-collaboration tool.
CREATE TABLE IF NOT EXISTS `sm_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sm_submission_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` bigint(20) NOT NULL,
  `status` int(11) NOT NULL,
  `date_updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `submission_id` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sm_submissions_status_updates` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `submissionid` bigint(20) NOT NULL,
  `editorid` bigint(20) NOT NULL,
  `date_updated` datetime NOT NULL,
  `previous_status` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `note` mediumtext NOT NULL,
  `iscurrent` tinyint(1) NOT NULL DEFAULT 1,
  `ischecked` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `submissionid` (`submissionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Revision collaboration (editor <-> writer message threads)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_revisions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `submission_id` bigint(20) NOT NULL,
  `sender_id` bigint(20) NOT NULL,
  `sender_type` varchar(20) NOT NULL,
  `message` text DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `original_filename` varchar(255) DEFAULT NULL,
  `date_sent` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `submission_id` (`submission_id`),
  KEY `date_sent` (`date_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Reader ratings
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_submissions_ratings` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `submission_id` bigint(20) NOT NULL,
  `reader_id` bigint(20) NOT NULL,
  `rating` int(11) NOT NULL,
  `date_rated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `submission_id` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Editor notes on submissions
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poemid` bigint(20) NOT NULL,
  `editorid` bigint(20) NOT NULL,
  `note` mediumtext NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `poemid` (`poemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Paid-submission credits (ledger; balance = SUM(amount))
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_credits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poet_id` bigint(20) NOT NULL,
  `amount` int(11) NOT NULL COMMENT '+N for grant, -1 for use',
  `reason` varchar(255) DEFAULT NULL,
  `granted_by_editor_id` bigint(20) DEFAULT NULL,
  `used_for_submission_id` bigint(20) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `poet_id` (`poet_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- PayPal contest-fee flow (pending submissions awaiting capture)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_paypal_pending` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `poet_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `attachment` varchar(255) NOT NULL DEFAULT '',
  `bio_comments` mediumtext DEFAULT NULL,
  `edition_id` int(11) NOT NULL,
  `ip_address` varchar(100) NOT NULL DEFAULT '',
  `expected_amount` decimal(8,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `paypal_order_id` varchar(64) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'created',
  `captured_submission_id` bigint(20) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `apply_credit` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `paypal_order_id` (`paypal_order_id`),
  KEY `poet_id` (`poet_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------------
-- Staff password resets (writers use token columns on sm_poets)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `token` varchar(128) NOT NULL,
  `expiry` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Settings (key/value)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sm_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `var` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `var` (`var`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mail_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `paypal_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ===========================================================================
-- Seed data
-- ===========================================================================

INSERT IGNORE INTO `sm_status` (`id`, `status`) VALUES
(1, 'Accepted'),
(2, 'Rejected'),
(3, 'Revision Requested');

INSERT IGNORE INTO `sm_settings` (`var`, `value`) VALUES
('accepted_message', 'We appreciate your submission. It is a pleasure to publish your work.'),
('notification_emails', ''),
('notification_email_enabled', '0'),
('open_submissions', '1'),
('volunteer_readers', 'no'),
('volunteer_reader_in_footer', 'no'),
('reader_notes', 'yes'),
('blind_editor', 'no'),
('blind_reader_comments', 'yes'),
('show_payment_types', 'yes'),
('show_ip_address', 'yes'),
('show_score', 'yes'),
('contest_tab', 'yes'),
('paid_only', 'no'),
('final_check', 'yes'),
('enable_payments', 'no'),
('show_payment_amount', 'yes'),
('allow_downloads', 'no'),
('allow_withdraws', 'yes'),
('site_maintenance', 'no'),
('user_registration', 'yes'),
('comment_moderation', 'no'),
('allowed_file_types', '.txt, .doc, .docx'),
('readertos_url', ''),
('payment_to_authors', '0');

INSERT IGNORE INTO `mail_settings` (`setting_key`, `setting_value`) VALUES
('mail_host', ''),
('mail_port', '465'),
('mail_username', ''),
('mail_password', ''),
('mail_from_address', ''),
('mail_from_name', ''),
('mail_encryption', 'ssl');

INSERT IGNORE INTO `paypal_settings` (`setting_key`, `setting_value`) VALUES
('environment', 'sandbox'),
('client_id', ''),
('secret', ''),
('currency', 'USD'),
('enabled', '0');
