-- Database migration script to add TV View and Currency Order columns
-- Created: December 7, 2025

USE test1;

-- Add tvView column for Display on TV functionality
ALTER TABLE `currencyMaster` 
ADD COLUMN `tvView` CHAR(1) DEFAULT 'I' COMMENT 'Display on TV: A=Active, I=Inactive' AFTER `homeView`;

-- Add currOrder column for Currency ordering
ALTER TABLE `currencyMaster` 
ADD COLUMN `currOrder` INT(11) DEFAULT 1 COMMENT 'Order of currency display' AFTER `tvView`;

-- Update existing records to have default order based on current ID
UPDATE `currencyMaster` SET `currOrder` = `id` WHERE `currOrder` IS NULL OR `currOrder` = 0;

-- Create index on currOrder for better performance on sorting
CREATE INDEX `idx_currOrder` ON `currencyMaster` (`currOrder`);

-- Verify the changes
-- SELECT id, baseCurr, toCurr, homeView, tvView, currOrder FROM currencyMaster LIMIT 10;
-- Database migration script for Media Management
-- Created: December 7, 2025

USE test1;

-- Create media master table for images and video

CREATE TABLE `mediaMaster` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `fileName` VARCHAR(255) NOT NULL COMMENT 'Original file name',
  `filePath` VARCHAR(500) NOT NULL COMMENT 'Path to uploaded file',
  `fileType` ENUM('image', 'video') NOT NULL DEFAULT 'image' COMMENT 'Type of media',
  `mediaOrder` INT(11) DEFAULT 1 COMMENT 'Display order',
  `status` CHAR(1) NOT NULL DEFAULT 'A' COMMENT 'A=Active, I=Inactive, D=Deleted',
  `createdOn` DATETIME DEFAULT NULL,
  `createdBy` INT(11) DEFAULT NULL,
  `modifiedOn` DATETIME DEFAULT NULL,
  `modifiedBy` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_fileType` (`fileType`),
  KEY `idx_status` (`status`),
  KEY `idx_mediaOrder` (`mediaOrder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Media management for images and videos';

-- Create settings table for display mode

CREATE TABLE `mediaSettings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `settingKey` VARCHAR(50) NOT NULL COMMENT 'Setting identifier',
  `settingValue` VARCHAR(255) NOT NULL COMMENT 'Setting value',
  `description` VARCHAR(500) DEFAULT NULL,
  `modifiedOn` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_settingKey` (`settingKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Settings for media display';

-- Insert default display mode setting
INSERT INTO `mediaSettings` (`settingKey`, `settingValue`, `description`, `modifiedOn`) 
VALUES ('display_mode', 'full_rate', 'Display mode: full_rate, rate_and_image, rate_and_video', NOW());

-- Verify the changes
-- SELECT * FROM mediaMaster;
-- SELECT * FROM mediaSettings;
mkdir -p /var/msp/forex/public/ads
chmod 755 /var/msp/forex/public/ads