

CREATE TABLE IF NOT EXISTS properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  coords VARCHAR(64),
  built_date DATE,
  area DECIMAL(10,2),
  bedrooms INT DEFAULT 0,
  bathrooms INT DEFAULT 0,
  floor INT DEFAULT 0,
  price DECIMAL(14,2) DEFAULT 0,
  sea_view TINYINT(1) DEFAULT 0,
  description TEXT,
  manager_name VARCHAR(255),
  manager_phone VARCHAR(64),
  views_count INT DEFAULT 0,
  leads_count INT DEFAULT 0,
  rent_per_month DECIMAL(12,2) DEFAULT 0,
  occupancy_rate DECIMAL(5,2) DEFAULT 0,
  maintenance_percent DECIMAL(5,2) DEFAULT 0,
  tax_percent DECIMAL(5,2) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS property_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS property_payment_schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  seq_number INT NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS property_views (
  id INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  day DATE NOT NULL,
  views INT DEFAULT 0,
  UNIQUE KEY (property_id, day),
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(64),
  property_id INT,
  status ENUM('new','in_progress','called','won','lost') DEFAULT 'new',
  note TEXT,
  called TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Demo data
INSERT INTO properties (title,address,coords,area,bedrooms,bathrooms,floor,price,sea_view,description,manager_name,manager_phone,views_count,leads_count,rent_per_month,occupancy_rate,maintenance_percent,tax_percent)
VALUES
('Skyline Apartments 12B','123 Ocean Drive, Miami','25.7617,-80.1918',85,2,2,12,450000,1,'Вид на океан, панорамные окна','Анастасия','+1 (305) 555-0100',1200,45,3500,80,10,5),
('Old Town Loft','Gravenstraat 12, Amsterdam','52.3738,4.8910',64,1,1,3,390000,0,'Исторический центр, кирпичные стены','Peter','+31 (0)20 555 0123',980,32,2800,75,8,4);

INSERT INTO property_images (property_id, file_name, sort_order) VALUES
(1,'demo1.jpg',0),(1,'demo2.jpg',1),(2,'demo3.jpg',0);

INSERT INTO leads (name,email,phone,property_id,status,note,called,sort_order) VALUES
('Иван','ivan@example.com','+7 (999) 111-22-33',1,'new','Хочу посмотреть в выходные',0,0),
('Мария','maria@example.com','+7 (999) 222-33-44',2,'in_progress','Запросила планировку',1,1),
('John','john@example.com','+1 (555) 123-4567',1,'called','Обсудили ипотеку',1,2);

-- sample property views for last few days
INSERT INTO property_views (property_id, day, views) VALUES
(1, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 100),
(1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 120),
(1, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 90),
(1, CURDATE(), 80),
(2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), 60),
(2, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 40),
(2, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 55),
(2, CURDATE(), 70);
