CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE properties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  address1 VARCHAR(190) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(50) NULL,
  postal_code VARCHAR(20) NULL,
  county_parish VARCHAR(100) NULL,
  parcel_number VARCHAR(100) NULL,
  legal_description TEXT NULL,
  estimated_value DECIMAL(14,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_property_address (address1, city, state),
  INDEX idx_parcel (parcel_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_type ENUM('person','company','government') DEFAULT 'person',
  first_name VARCHAR(100) NULL,
  middle_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  company_name VARCHAR(190) NULL,
  role VARCHAR(100) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  mailing_address1 VARCHAR(190) NULL,
  mailing_city VARCHAR(100) NULL,
  mailing_state VARCHAR(50) NULL,
  mailing_postal_code VARCHAR(20) NULL,
  do_not_contact TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_contact_email (email),
  INDEX idx_contact_name (last_name, first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NULL,
  primary_contact_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NULL,
  lead_type VARCHAR(100) NOT NULL DEFAULT 'General',
  source VARCHAR(190) NULL,
  source_record_number VARCHAR(100) NULL,
  status ENUM('New','Needs Research','Ready to Contact','Contacted','Follow-Up Due','Responded','Interested','Nurture','Closed','Not Interested','Do Not Contact') DEFAULT 'New',
  priority ENUM('Low','Normal','High','Urgent') DEFAULT 'Normal',
  amount_owed DECIMAL(14,2) NULL,
  sale_date DATE NULL,
  next_follow_up_at DATETIME NULL,
  follow_up_reason VARCHAR(255) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_lead_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL,
  CONSTRAINT fk_lead_contact FOREIGN KEY (primary_contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  INDEX idx_lead_status (status),
  INDEX idx_follow_up (next_follow_up_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE government_entities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agency_name VARCHAR(190) NOT NULL,
  agency_type VARCHAR(100) NULL,
  jurisdiction VARCHAR(190) NULL,
  department VARCHAR(190) NULL,
  contact_name VARCHAR(190) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  mailing_address TEXT NULL,
  website VARCHAR(255) NULL,
  available_lists TEXT NULL,
  preferred_request_method VARCHAR(100) NULL,
  request_frequency VARCHAR(100) NULL,
  last_request_at DATETIME NULL,
  last_response_at DATETIME NULL,
  next_request_at DATETIME NULL,
  follow_up_days INT DEFAULT 7,
  status ENUM('Active','Paused','Inactive') DEFAULT 'Active',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_government_next_request (next_request_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE email_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  audience ENUM('lead','government','both') DEFAULT 'both',
  subject VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE communications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NULL,
  government_entity_id BIGINT UNSIGNED NULL,
  contact_id BIGINT UNSIGNED NULL,
  communication_type ENUM('email','phone','note','mail','other') DEFAULT 'note',
  direction ENUM('outbound','inbound','internal') DEFAULT 'internal',
  subject VARCHAR(255) NULL,
  body TEXT NULL,
  sent_at DATETIME NULL,
  outcome VARCHAR(190) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comm_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  CONSTRAINT fk_comm_government FOREIGN KEY (government_entity_id) REFERENCES government_entities(id) ON DELETE CASCADE,
  CONSTRAINT fk_comm_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NULL,
  government_entity_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  due_at DATETIME NULL,
  status ENUM('Open','Completed','Cancelled') DEFAULT 'Open',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_government FOREIGN KEY (government_entity_id) REFERENCES government_entities(id) ON DELETE CASCADE,
  INDEX idx_task_due (due_at, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE import_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  list_type VARCHAR(100) NULL,
  header_signature CHAR(64) NULL,
  mapping_json JSON NOT NULL,
  fixed_values_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_header_signature (header_signature)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE import_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  list_type VARCHAR(100) NULL,
  source VARCHAR(190) NULL,
  import_profile_id BIGINT UNSIGNED NULL,
  total_rows INT DEFAULT 0,
  imported_rows INT DEFAULT 0,
  skipped_rows INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_batch_profile FOREIGN KEY (import_profile_id) REFERENCES import_profiles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO email_templates (name, audience, subject, body) VALUES
('General Property Inquiry', 'lead', 'Question about {{property_address}}', 'Hello {{owner_name}},\n\nI am reaching out regarding the property located at {{property_address}}. I would appreciate the opportunity to speak with you about it.\n\nPlease reply to this email or call me at {{sender_phone}}.\n\nThank you,\n{{sender_name}}'),
('Public Records List Request', 'government', 'Public records request for {{list_type}}', 'Hello {{contact_name}},\n\nI am requesting the most recent available {{list_type}} for {{jurisdiction}}. Please let me know whether the records are available electronically and whether any fee applies.\n\nThank you,\n{{sender_name}}');