Postgres Tables

class User(AbstractBaseUser, PermissionsMixin):
    """
    A modification of the built-in Django user that:
        - switches first_name & last_name for username & full_name
        - keeps other admin-compliant options

    Has connections from:

    - Favorites

    Attributes:
        email (Email, unique): This doubles as the username.
        phone (Varchar): User's phone number, used for notifications and dual authentication.
        phone_verified (Boolean): Indicates whether the user's phone number is verified.
        username (Varchar, unique): User's username, used for login and identification.
        full_name (Varchar): User's full name, used for display purposes.
        is_staff (Boolean): Indicates whether the user can log into the admin site.
        is_active (Boolean): Indicates whether the user is active. Unselect this instead of deleting accounts.
        is_subscribed (Boolean): Indicates whether the user is subscribed to notifications.
        date_joined (DateTime): The date and time when the user joined the platform.
    """

    username_validator = UnicodeUsernameValidator()

    email = models.EmailField(_("email address"), unique=EMAIL_REQUIRED, default="")
    phone = models.CharField(
        _("phone number"),
        max_length=20,
        blank=True,
        unique=True,
    )

    phone_verified = models.BooleanField(
        _("phone verified"),
        default=False,
        help_text=_("Designates whether this user's phone number is verified."),
    )

    username = models.CharField(
        max_length=255,
        unique=True,
        validators=[username_validator] if USERNAME_REQUIRED else [],
        default="",
    )
    full_name = models.CharField(_("full name"), max_length=150, blank=True)
    is_staff = models.BooleanField(
        _("staff status"),
        default=False,
        help_text=_("Designates whether the user can log into this admin site."),
    )
    is_active = models.BooleanField(
        _("active"),
        default=True,
        help_text=_(
            "Designates whether this user should be treated as active. "
            "Unselect this instead of deleting accounts."
        ),
    )

    is_subscribed = models.BooleanField(
        _("subscribed"),
        default=True,
        help_text=_("Designates whether the user is subscribed to notifications."),
    )

    date_joined = models.DateTimeField(_("date joined"), default=timezone.now)

    objects = OkUserManager()

    EMAIL_FIELD = "email"
    USERNAME_FIELD = "username" if USERNAME_REQUIRED else "email"
    REQUIRED_FIELDS = []

    class Meta:
        verbose_name = _("user")
        verbose_name_plural = _("users")

    def clean(self):
        super().clean()
        self.email = self.__class__.objects.normalize_email(self.email)

    def save(self, *args, **kwargs):
        if EMAIL_REQUIRED and not self.username:
            self.username = self.email
        super().save(*args, **kwargs)

    def get_short_name(self):
        return self.username

    def get_full_name(self):
        return self.full_name

    def email_user(self, subject, message, from_email=None, **kwargs):
        send_mail(subject, message, from_email, [self.email], **kwargs)

class ActionsTable(models.Model):
    """
    Stores each distinct action taken on a bill, e.g., ("First Reading").
    Represented by a one-to-many relationship between bill and actions.

    This table is queried by frontend views that show bill information, such
    as most recent action. Additionally, it will be queried by the
    notification system, which updates users about favorited bills that have
    had a significant action associated with them in the past 24 hours.

    Connects to:

    - Bills (on bill_id ForeignKey)

    Attributes:
        action_id (Varchar, PrimaryKey, unique):
            Unique identifier for the action.
        bill_id (ForeignKey):
            `bill_id` from the bills table for the bill associated with this action.
        description (Varchar): Description of the action, e.g., ("First Reading").
        date (Timestamp): Date of the action.
        category (Varchar, nullable):
            Category of the action. Used to group actions into broader types for
            tracking bill status.
    """

    action_id = models.CharField(unique=True, primary_key=True, db_column="action_id")
    bill_id = models.ForeignKey("BillsTable", on_delete=models.CASCADE, db_column="bill_id")
    description = models.CharField()
    date = models.DateTimeField()
    category = models.CharField(null=True)
    chamber = models.CharField(null=True, default=None)

    class Meta:
        db_table = "actions_table"
        unique_together = ("action_id", "bill_id")

class BillsTable(models.Model):
    """
    Stores data for each bill.

    This table is queried by frontend views that show bill information, such
    as the search view and individual bill pages.

    Has connections from:

    - Actions
    - Favorites
    - Sponsors
    - Topics

    Attributes:
        bill_id (Varchar, PrimaryKey, unique):
            Unique identifier for the bill, sourced from OpenStates.
        number (Varchar): Bill number used by the legislature.
        title (Varchar): Official title of the bill.
        state (Varchar): State where the bill is introduced.
        session (Varchar): Legislative session in which the bill is introduced.
        summary (Varchar): Bill summary as sourced from OpenStates API.
        status (Varchar): The latest action taken on the bill.
    """

    bill_id = models.CharField(unique=True, primary_key=True)
    number = models.CharField()
    title = models.CharField()
    state = models.CharField()
    session = models.CharField()
    summary = models.CharField()
    status = models.CharField()

    class Meta:
        db_table = "bills_table"

class FavoritesTable(models.Model):
    """
    Stores data for user favorites of bills. Represents a many-to-many
    relationship: one user can like many bills, one bill can be associated
    with many users.

    This table will be queried by frontend views that show users which bills
    they have favorited. Additionally, this table will be used for the
    automatic notification system that notifies users about updates from
    bills they have favorited.

    Connects to:

    - Bills (on bill_id ForeignKey)
    - Users (on user_id ForeignKey)

    Attributes:
        id (Bigint, PrimaryKey): Internal ID for a favorite.
        bill_id (Varchar, ForeignKey):
            `bill_id` from the bills table for the bill favorited.
        user_id (Varchar, ForeignKey):
            `user_id` from the users table for the user favoriting the bill.
    """

    user_id = models.ForeignKey(User, on_delete=models.CASCADE, db_column="user_id")
    bill_id = models.ForeignKey("BillsTable", on_delete=models.CASCADE, db_column="bill_id")

    class Meta:
        db_table = "favorites_table"
        unique_together = ("user_id", "bill_id")

class SponsorsTable(models.Model):
    """
    Stores data for sponsors of bills. Represents a many-to-many relationship:
    one bill may have many sponsors, and one sponsor may have many bills.

    This table is queried by frontend views that show bill information,
    including sponsor information.

    Connects to:

    - Bills (on bill_id ForeignKey)

    Attributes:
        id (Varchar, PrimaryKey, unique):
            Internal id of the sponsor. Separate from sponsor_id
            as sponsor_id comes from OpenStates and may be null.
        sponsor_id (Varchar): sponsor_id from OpenStates.
        sponsor_name (Varchar): Name of the bill sponsor.
        bill_id (Varchar, ForeignKey):
            `bill_id` from the bills table, the bill that the sponsor has sponsored.
        position (Varchar, nullable):
            The position (e.g., Member of the State House, Member
            of the State Senate) that the sponsor occupies.
        party (Varchar, nullable): The political party of the sponsor.
    """

    id = models.CharField(unique=True, primary_key=True)
    bill_id = models.ForeignKey("BillsTable", on_delete=models.CASCADE, db_column="bill_id")
    sponsor_id = models.CharField(null=True)
    sponsor_name = models.CharField()
    position = models.CharField(null=True)
    party = models.CharField(null=True)

    class Meta:
        db_table = "sponsors_table"

class TopicsTable(models.Model):
    """
    Stores data for topics associated with each bill. Represents a many-to-many relationship:
    one bill may have many topics, one topic may have many bills associated with it.

    This table is queried by frontend views that show bill information,
    including topic information.

    Connects to:

    - Bills (on bill_id ForeignKey)

    Attributes:
        id (Bigint, PrimaryKey): ID of the topic.
        topic (Varchar): Topic name.
        bill_id (Varchar, ForeignKey):
            `bill_id` from the bills table, the bill the topic is associated with.
    """

    bill_id = models.ForeignKey("BillsTable", on_delete=models.CASCADE, db_column="bill_id")
    topic = models.CharField()

    class Meta:
        db_table = "topics_table"

class UpdatesTable(models.Model):
    """
    A table storing periodic updates for bills.

    This table is included in addition to the ActionsTable to provide a simple way to
    to use the most recent action for notifications.

    Connects to:

    - Actions (on action_id ForeignKey)
    - Bills (on bill_id ForeignKey)

    Attributes:
        action_id (Varchar, ForeignKey):
            `action_id` from the actions table, the bill the update is associated with.
        topic (Varchar): Topic name.
        bill_id (Varchar, ForeignKey):
            `bill_id` from the bills table, the bill the update is associated with.
        description (Varchar): Description of the update.
        date (DateTime): Date of the update.
        category (Varchar, nullable): Category of the update.
        chamber (Varchar): Chamber (House or Senate) associated with the update.
    """

    action_id = models.ForeignKey("ActionsTable", on_delete=models.CASCADE, db_column="action_id")
    bill_id = models.ForeignKey("BillsTable", on_delete=models.CASCADE, db_column="bill_id")
    description = models.CharField()
    date = models.DateTimeField()
    category = models.CharField(null=True)
    chamber = models.CharField()

    class Meta:
        db_table = "updates_table"
        unique_together = ("action_id", "bill_id")

class MostRecentUpload(models.Model):
    """
    A table that stores the most recent date that bills data were uploaded.

    Used to determine whether or not a notification email should be sent, or
    if there was an error uploading the bills.

    Connects to:

    - None

    Attributes:
        id (Bigint, PrimaryKey): ID of the upload.
        last_upload_date (Date): Date of the last successful bill upload.
    """

    last_upload_date = models.DateField()

    class Meta:
        db_table = "most_recent_upload"

class UserNotificationQueue(models.Model):
    """
    A table to store user notifications in a queue, with all bills to update on bundled in a single notification.

    Connects to:

    - Users (on user_id)

    Attributes:
        id (Bigint, PrimaryKey): ID of the queue entry.
        user_id (ForeignKey): The user_id of the user to be notified.
        number_of_notifications (Int): Number of notifications for a user on a given day.
        bills_to_notify (Array): A list of the bill ids that the user needs to be updated on.
        is_notified (Boolean): Whether or not a notification email has been sent to the user.
    """

    user_id = models.OneToOneField(User, on_delete=models.CASCADE, db_column="user_id")
    number_of_notifications = models.IntegerField()
    bills_to_notify = models.JSONField()
    is_notified = models.BooleanField(default=False)

    class Meta:
        db_table = "user_notification_queue"