-- Create user and grant privileges
DROP USER IF EXISTS 'app_user'@'localhost';
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Secure@Password123';
GRANT ALL PRIVILEGES ON real_estate.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

-- Create the database
CREATE DATABASE IF NOT EXISTS `real_estate`;
USE `real_estate`;

-- Table structure for table `owner`
CREATE TABLE IF NOT EXISTS `owner` (
  `owner_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `contact` VARCHAR(20) UNIQUE,
  `email` VARCHAR(255) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserting 5 sample records into `owner`
INSERT INTO `owner` (`name`, `contact`, `email`) VALUES
('John Smith', '111-222-3333', 'john.smith@example.com'),
('Jane Doe', '444-555-6666', 'jane.doe@example.com'),
('Peter Jones', '777-888-9999', 'peter.jones@example.com'),
('Mary Williams', '123-456-7890', 'mary.williams@example.com'),
('David Brown', '098-765-4321', 'david.brown@example.com');

-- Table structure for table `agent`
CREATE TABLE IF NOT EXISTS `agent` (
  `agent_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `contact` VARCHAR(20) UNIQUE,
  `email` VARCHAR(255) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserting 5 sample records into `agent`
INSERT INTO `agent` (`name`, `contact`, `email`) VALUES
('Agent Alice', '101-202-3030', 'alice@realestate.com'),
('Agent Bob', '404-505-6060', 'bob@realestate.com'),
('Agent Charlie', '707-808-9090', 'charlie@realestate.com'),
('Agent Diana', '112-223-3344', 'diana@realestate.com'),
('Agent Eve', '556-667-7788', 'eve@realestate.com');

-- Table structure for table `clients`
CREATE TABLE IF NOT EXISTS `clients` (
  `client_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) UNIQUE,
  `email` VARCHAR(255) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserting 5 sample records into `clients`
INSERT INTO `clients` (`name`, `phone`, `email`) VALUES
('Client Chris', '121-232-3434', 'chris.c@client.com'),
('Client Dana', '454-565-6767', 'dana.d@client.com'),
('Client Frank', '787-898-9090', 'frank.f@client.com'),
('Client Grace', '212-323-4343', 'grace.g@client.com'),
('Client Heidi', '545-656-7676', 'heidi.h@client.com');

-- Table structure for table `properties`
CREATE TABLE IF NOT EXISTS `properties` (
  `property_id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `address` VARCHAR(255) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `status` ENUM('Available', 'Sold', 'Rented') NOT NULL DEFAULT 'Available',
  `owner_id` INT,
  `agent_id` INT,
  CONSTRAINT `fk_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner` (`owner_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_agent` FOREIGN KEY (`agent_id`) REFERENCES `agent` (`agent_id`) ON DELETE SET NULL,
  CHECK (`price` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserting 5 sample records into `properties`
INSERT INTO `properties` (`title`, `type`, `address`, `city`, `price`, `status`, `owner_id`, `agent_id`) VALUES
('Modern Downtown Loft', 'House', '123 Main St', 'Metropolis', 1200000.00, 'Available', 1, 1),
('Suburban Family Home', 'House', '456 Oak Ave', 'Smallville', 750000.00, 'Available', 2, 2),
('Luxury Beachfront Villa', 'House', '789 Ocean Dr', 'Coast City', 2500000.00, 'Sold', 3, 1),
('Cozy Countryside Cottage', 'House', '101 Pine Ln', 'Green Valley', 450000.00, 'Rented', 4, 3),
('High-Rise Apartment', 'Apartment', '212 Sky Blvd', 'Metropolis', 950000.00, 'Available', 5, 4);

-- Table structure for table `transactions`
CREATE TABLE IF NOT EXISTS `transactions` (
  `transaction_id` INT AUTO_INCREMENT PRIMARY KEY,
  `type` ENUM('Sale', 'Rent') NOT NULL,
  `amount` DECIMAL(12, 2) NOT NULL,
  `date` DATE NOT NULL,
  `property_id` INT,
  `agent_id` INT,
  `client_id` INT,
  UNIQUE (`property_id`), -- A property can only be in one final transaction
  CONSTRAINT `fk_trans_property` FOREIGN KEY (`property_id`) REFERENCES `properties` (`property_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_trans_agent` FOREIGN KEY (`agent_id`) REFERENCES `agent` (`agent_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_trans_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inserting 2 sample records into `transactions`
INSERT INTO `transactions` (`type`, `amount`, `date`, `property_id`, `agent_id`, `client_id`) VALUES
('Sale', 2450000.00, '2024-05-20', 3, 1, 3),
('Rent', 3000.00, '2024-06-01', 4, 3, 4);

-- SQL Views
-- View for Available Properties
CREATE OR REPLACE VIEW `AvailableProperties` AS
SELECT `property_id`, `title`, `type`, `address`, `city`, `price`
FROM `properties`
WHERE `status` = 'Available';

-- View for High Value Properties
CREATE OR REPLACE VIEW `HighValueProperties` AS
SELECT `property_id`, `title`, `type`, `address`, `city`, `price`
FROM `properties`
WHERE `price` > 1000000;

-- Trigger to update property status after a transaction
DELIMITER $$
CREATE TRIGGER `after_transaction_insert`
AFTER INSERT ON `transactions`
FOR EACH ROW
BEGIN
    IF NEW.type = 'Sale' THEN
        UPDATE `properties` SET `status` = 'Sold' WHERE `property_id` = NEW.property_id;
    ELSEIF NEW.type = 'Rent' THEN
        UPDATE `properties` SET `status` = 'Rented' WHERE `property_id` = NEW.property_id;
    END IF;
END$$
DELIMITER ;