-- معادل کامل آخرین وضعیت دیتابیس بعد از تمام Migration های EF Core
-- (InitializeDatabase -> AddMasterIntroductionModel -> AddStorePostsTable ->
--  AddTwoFieldForStores -> AddPhotoFieldForStore -> SetColumnTypeForValue ->
--  AddVoteTable -> UpdateStore)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `Users` (
  `Id` BIGINT NOT NULL COMMENT 'آیدی عددی تلگرام کاربر (نه Auto Increment)',
  `Status` INT NOT NULL DEFAULT 0,
  `CreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `RadioPosts` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `FromId` BIGINT NOT NULL,
  `Text` TEXT NOT NULL,
  `Status` SMALLINT NOT NULL DEFAULT 0,
  `CreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `IX_RadioPosts_FromId` (`FromId`),
  CONSTRAINT `FK_RadioPosts_Users_FromId` FOREIGN KEY (`FromId`) REFERENCES `Users`(`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `StorePosts` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `Title` TEXT NOT NULL,
  `Value` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT 'ستون مرده - همیشه ۰ می‌ماند، عمداً استفاده نمی‌شود (مطابق نسخه اصلی)',
  `Price` TEXT NOT NULL COMMENT 'مقدار واقعی «مبلغ/توضیحات» همینجاست',
  `Location` TEXT NOT NULL,
  `Username` TEXT NOT NULL,
  `Photo` TEXT NOT NULL,
  `Status` SMALLINT NOT NULL DEFAULT 0,
  `Type` SMALLINT NOT NULL DEFAULT 0,
  `FromId` BIGINT NOT NULL,
  `CreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `IX_StorePosts_FromId` (`FromId`),
  CONSTRAINT `FK_StorePosts_Users_FromId` FOREIGN KEY (`FromId`) REFERENCES `Users`(`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `MasterIntroductions` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `FromId` BIGINT NOT NULL,
  `Name` TEXT NOT NULL,
  `College` SMALLINT NOT NULL,
  `Type` SMALLINT NOT NULL,
  `Lectures` TEXT NOT NULL,
  `Integrity` INT NOT NULL,
  `Hegemony` INT NOT NULL,
  `Capacity` INT NOT NULL,
  `Management` INT NOT NULL,
  `Responsive` INT NOT NULL,
  `Behavior` INT NOT NULL,
  `RollCall` SMALLINT NOT NULL,
  `Resources` TEXT NOT NULL,
  `Exams` TEXT NOT NULL,
  `Score` SMALLINT NOT NULL,
  `Connections` TEXT NOT NULL,
  `Term` TEXT NOT NULL,
  `Descriptions` TEXT NOT NULL,
  `Status` SMALLINT NOT NULL DEFAULT 0,
  `CreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `IX_MasterIntroductions_FromId` (`FromId`),
  CONSTRAINT `FK_MasterIntroductions_Users_FromId` FOREIGN KEY (`FromId`) REFERENCES `Users`(`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `Votes` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `UserId` BIGINT NOT NULL,
  `MasterIntroductionId` BIGINT NOT NULL,
  `Type` SMALLINT NOT NULL DEFAULT 0,
  `CreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `IX_Votes_UserId` (`UserId`),
  KEY `IX_Votes_MasterIntroductionId` (`MasterIntroductionId`),
  CONSTRAINT `FK_Votes_Users_UserId` FOREIGN KEY (`UserId`) REFERENCES `Users`(`Id`),
  CONSTRAINT `FK_Votes_MasterIntroductions_MasterIntroductionId` FOREIGN KEY (`MasterIntroductionId`) REFERENCES `MasterIntroductions`(`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;