-- SQL Schema for Registration Admin Panel
-- Basudev Technologies

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------
-- Table `admins`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table `registrations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `registrations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `device_model` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('Pending', 'Approved', 'Complete', 'Reject') NOT NULL DEFAULT 'Pending',
  `device_ip` VARCHAR(45) DEFAULT NULL,
  `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table `registration_notes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `registration_notes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `registration_id` INT NOT NULL,
  `note_text` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_notes_reg`
    FOREIGN KEY (`registration_id`)
    REFERENCES `registrations` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table `status_logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `status_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `registration_id` INT NOT NULL,
  `old_status` VARCHAR(20) DEFAULT NULL,
  `new_status` VARCHAR(20) NOT NULL,
  `changed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_logs_reg`
    FOREIGN KEY (`registration_id`)
    REFERENCES `registrations` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- -----------------------------------------------------
-- Seed Data
-- -----------------------------------------------------

-- Admin seed (username: admin, password: admin123)
DELETE FROM `admins` WHERE `username` = 'admin';
INSERT INTO `admins` (`username`, `password`)
VALUES ('admin', '$2y$12$N5IZnS1y.qtYLOh1cvA5CezqZQA.79IQwT1ppN3MWM9LjqhXHkPVW');

-- Realistic Dummy Registrations (spread across last 30 days)
INSERT INTO `registrations` (`full_name`, `phone`, `email`, `device_model`, `status`, `device_ip`, `registered_at`) VALUES
('Amit Sharma', '9876543210', 'amit.sharma@example.com', 'Samsung Galaxy S21', 'Approved', '192.168.1.1', DATE_SUB(NOW(), INTERVAL 25 DAY)),
('Priya Patel', '9876543211', 'priya.patel@example.com', 'OnePlus 9', 'Pending', '192.168.1.2', DATE_SUB(NOW(), INTERVAL 20 DAY)),
('Rahul Verma', '9876543212', 'rahul.verma@example.com', 'Xiaomi Mi 11', 'Complete', '192.168.1.3', DATE_SUB(NOW(), INTERVAL 15 DAY)),
('Sneha Gupta', '9876543213', 'sneha.gupta@example.com', 'iPhone 13', 'Reject', '192.168.1.4', DATE_SUB(NOW(), INTERVAL 10 DAY)),
('Vikram Singh', '9876543214', 'vikram.singh@example.com', 'Samsung Galaxy S22', 'Approved', '192.168.1.5', DATE_SUB(NOW(), INTERVAL 5 DAY)),
('Anjali Nair', '9876543215', 'anjali.nair@example.com', 'Google Pixel 6', 'Pending', '192.168.1.6', DATE_SUB(NOW(), INTERVAL 2 DAY)),
('Rohan Das', '9876543216', 'rohan.das@example.com', 'Realme 8', 'Complete', '192.168.1.7', DATE_SUB(NOW(), INTERVAL 28 DAY)),
('Meera Joshi', '9876543217', 'meera.joshi@example.com', 'iPhone 12', 'Approved', '192.168.1.8', DATE_SUB(NOW(), INTERVAL 12 DAY)),
('Karan Malhotra', '9876543218', 'karan.malhotra@example.com', 'OnePlus 8T', 'Pending', '192.168.1.9', DATE_SUB(NOW(), INTERVAL 1 DAY)),
('Sonia Rao', '9876543219', 'sonia.rao@example.com', 'Vivo V21', 'Reject', '192.168.1.10', DATE_SUB(NOW(), INTERVAL 18 DAY)),
('Arun Kumar', '9876543220', 'arun.kumar@example.com', 'Oppo Reno 6', 'Complete', '192.168.1.11', DATE_SUB(NOW(), INTERVAL 7 DAY)),
('Ishani Bose', '9876543221', 'ishani.bose@example.com', 'Samsung Galaxy Note 20', 'Approved', '192.168.1.12', DATE_SUB(NOW(), INTERVAL 3 DAY)),
('Deepak Mishra', '9876543222', 'deepak.mishra@example.com', 'iPhone 13 Pro', 'Pending', '192.168.1.13', DATE_SUB(NOW(), INTERVAL 22 DAY)),
('Kavita Reddy', '9876543223', 'kavita.reddy@example.com', 'Xiaomi Redmi Note 10', 'Complete', '192.168.1.14', DATE_SUB(NOW(), INTERVAL 14 DAY)),
('Suresh Pillai', '9876543224', 'suresh.pillai@example.com', 'OnePlus Nord 2', 'Approved', '192.168.1.15', DATE_SUB(NOW(), INTERVAL 9 DAY));